package edu.cs.usask.ca.silver.persistence.datasource;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.StringWriter;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.SQLSyntaxErrorException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.UUID;

import javax.sql.DataSource;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import junit.framework.Assert;

import org.apache.log4j.Logger;
import org.w3c.dom.Document;
import org.w3c.dom.Element;

import com.atomikos.jdbc.AtomikosSQLException;

import edu.cs.usask.ca.silver.api.persistence.StorageI;
import edu.cs.usask.ca.silver.exceptions.UnableToInitializeStorageException;
import edu.cs.usask.ca.silver.persistence.sql.DerbySQLCreateTableStringsI;
import edu.cs.usask.ca.silver.persistence.sql.DerbySQLDropTableStringsI;
import edu.cs.usask.ca.silver.persistence.sql.DerbySQLProcedureStringsI;
import edu.cs.usask.ca.silver.persistence.sql.DerbySQLFileStringsI;
import edu.cs.usask.ca.silver.persistence.sql.SQLGroupStringsI;
import edu.cs.usask.ca.silver.persistence.sql.SQLModelProjectStringsI;
import edu.cs.usask.ca.silver.persistence.sql.SQLModelVersionStringsI;
import edu.cs.usask.ca.silver.persistence.sql.SQLParameterSetStringsI;
import edu.cs.usask.ca.silver.persistence.sql.SQLParameterStringsI;
import edu.cs.usask.ca.silver.persistence.sql.SQLScenarioCollectionStringsI;
import edu.cs.usask.ca.silver.persistence.sql.SQLScenarioStringsI;
import edu.cs.usask.ca.silver.persistence.sql.SQLUserStringsI;

/**
 * A derby database implementation of the Storage interface using JDBC two phase
 * commit. All strings that are inserted into the database must be encoded using
 * org.apache.commons.codec.net.BCodec which is an implementation of the Base64
 * algorithm for strings. Strings are automatically decoded upon retrieval from
 * the database. All storage routines are transactional and can be embedded into
 * bigger transactions.
 * 
 * ** RENAMING THIS CLASS MAY BREAK SUBSTANTIAL STORED PROCEDURES!
 * 
 * @author Daniel Funk
 * @author Yudi Xue
 * 
 */
public class RemoteDerbyStorage extends AbstractStorage implements StorageI,
		DerbySQLCreateTableStringsI, DerbySQLDropTableStringsI,
		DerbySQLProcedureStringsI, SQLModelProjectStringsI,
		SQLModelVersionStringsI, SQLScenarioCollectionStringsI,
		SQLScenarioStringsI, SQLParameterSetStringsI, SQLParameterStringsI,
		DerbySQLFileStringsI, SQLUserStringsI, SQLGroupStringsI {

	private DataSource ds;
	private final HashMap<String, DataSource> map;
	private static final RemoteDerbyStorage storage = new RemoteDerbyStorage();
	private static Logger logger = Logger.getLogger(RemoteDerbyStorage.class);

	private RemoteDerbyStorage() {
		map = new HashMap<String, DataSource>();
	}

	private void addRemoteDerbyDataSource(String serverName,
			String databaseName, int portNumber, String user, String pass,
			int poolSize, int connTimeOut) throws Exception {
		String resourceName = getRemoteResourceName(serverName, portNumber,
				databaseName);

		DataSource ds = DataSourceFactory.createNetworkClientDerbyDataSource(
				databaseName, serverName, portNumber, user, pass, poolSize,
				connTimeOut);

		map.put(resourceName, ds);
		setRemoteDS(serverName, databaseName, portNumber, user, pass, poolSize,
				connTimeOut);
	}

	private void addLocalDerbyDataSource(String databaseLoc, String user,
			String pass, int poolSize, int connTimeOut) throws Exception {

		String resourceName = getLocalResourceName(databaseLoc);

		DataSource ds = DataSourceFactory.createEmbeddedDerbyDataSource(
				databaseLoc, user, pass, poolSize, connTimeOut);
		map.put(resourceName, ds);
		setLocalDS(databaseLoc, user, pass, poolSize, connTimeOut);
	}

	private void setLocalDS(String databaseLoc, String user, String pass,
			int poolSize, int connTimeOut) throws Exception {
		final String resoureceName = getLocalResourceName(databaseLoc);
		if (map.containsKey(resoureceName)) {
			this.ds = map.get(resoureceName);
			this.ds.getConnection();
		} else {
			addLocalDerbyDataSource(databaseLoc, user, pass, poolSize,
					connTimeOut);
		}

	}

	private void setRemoteDS(String serverName, String databaseName,
			int portNumber, String user, String pass, int poolSize,
			int connTimeOut) throws Exception {

		String resource = getRemoteResourceName(serverName, portNumber,
				databaseName);

		if (map.containsKey(resource)) {
			this.ds = map.get(resource);
			this.ds.getConnection();
		} else {
			addRemoteDerbyDataSource(serverName, databaseName, portNumber,
					user, pass, poolSize, connTimeOut);
		}
	}

	private String getLocalResourceName(String databaseLoc) {
		return "local/" + databaseLoc;
	}

	private String getRemoteResourceName(String serverName, int portNumber,
			String databaseName) {
		return "remote/" + serverName + portNumber + databaseName;
	}

	public static StorageI getremoteStorage(String serverLoc,
			String databaseName, int port, String user, String pass,
			int poolSize, int connTimeOut) throws UnableToInitializeStorageException, Exception {

		if (serverLoc == null || databaseName == null || port < 0
				|| user == null || pass == null || poolSize < 0
				|| connTimeOut < 0) {
			throw new Exception(
					"one or more of the input parameters are null or less than 0");
		}
		try {
		storage.setRemoteDS(serverLoc, databaseName, port, user, pass,
				poolSize, connTimeOut);
		} catch (AtomikosSQLException ae){
			throw new UnableToInitializeStorageException ("Failed to initialize connection for REMOTE database, database is down or unreachable.", ae);
		}

		return storage;
	}

	@Override
	public boolean pingConnection() throws SQLException {
		return pingConnection(ds);
	}
	
	@Override
	public DataSource getCurrentDS () throws Exception {
		if (this.ds == null)
			throw new NullPointerException();
		return this.ds;
	}


	/**
	 * Return a preinitialized instance of database manager.
	 * 
	 * @return a storageI instance.
	 */
	public static StorageI getLocalStorage(String databaseLoc, String user,
			String pass, int poolSize, int connTimeOut) throws Exception {
		storage.setLocalDS(databaseLoc, user, pass, poolSize, connTimeOut);
		return storage;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.cs.usask.ca.silver.persistence.datasource.StorageI#initializeStorage
	 * ()
	 */
	public boolean initializeStorage() throws Exception {
		Connection conn = null;
		Statement stmt = null;
		boolean error = false;

		try {
			conn = getConnection(ds);
			stmt = conn.createStatement();
			stmt.execute(MODEL_PROJECT_SELECT_ANY);
			stmt.close();

		} catch (SQLSyntaxErrorException e) {
			return createStorage();
		} finally {
			try {
				closeConnection(ds, conn, error);
			} catch (Exception e) {
				error = true;
				throw e;
			}
		}

		return !error;
	}

	protected boolean createStorage() throws Exception {
		Connection conn = null;
		Statement stmt = null;
		boolean error = false;

		try {
			conn = getConnection(ds);
			stmt = conn.createStatement();
			stmt.addBatch(CREATE_TABLE_USER);
			stmt.addBatch(CREATE_TABLE_GROUP);
			stmt.addBatch(CREATE_TABLE_LOG_CHANGESET);
			stmt.addBatch(CREATE_TABLE_COMMENT);
			stmt.addBatch(CREATE_TABLE_MPROJECT);
			stmt.addBatch(CREATE_TABLE_MVERSION);
			stmt.addBatch(CREATE_TABLE_SCENARIOCOLLECTION);
			stmt.addBatch(CREATE_TABLE_PARAMETER);
			stmt.addBatch(CREATE_TABLE_PARAMETERSET);
			stmt.addBatch(CREATE_TABLE_P2PS);
			stmt.addBatch(CREATE_TABLE_SCENARIO);
			stmt.addBatch(CREATE_TABLE_FILE);
			stmt.addBatch(CREATE_TABLE_INPUTFILES);
			stmt.addBatch(CREATE_TABLE_TRAJECTORYFILES);
			stmt.addBatch(CREATE_TABLE_ANALYSISFILES);
			stmt.addBatch(CREATE_TABLE_MP2F);
			stmt.addBatch(CREATE_TABLE_MV2F);
			stmt.addBatch(CREATE_TABLE_S2F);
			stmt.addBatch(CREATE_TABLE_SC2F);
			stmt.addBatch(CREATE_TABLE_U2GROUP);
			stmt.addBatch(ADDMVORIGINALSCENARIOCONSTRAINT);
			stmt.addBatch(ADDMVBASELINESCENARIOCONSTRAINT);
			stmt.addBatch(ADDMVSOURCEFILECONSTRAINT);
			stmt.addBatch(ADDUSERPRIVATEGROUPCONSTRAINT);
			stmt.addBatch(REMOTE_CREATE_PROCEDURE_SELECT_ALL_USER_IDS);
			stmt.addBatch(REMOTE_CREATE_PROCEDURE_DUPLICATE_PARAMETER);
			stmt.addBatch(REMOTE_CREATE_PROCEDURE_DUPLICATE_PARAMETERS_BETWEEN_PS);
			stmt.addBatch(REMOTE_CREATE_PROCEDURE_DUPLICATE_TRAJECTORY_FILES_BETWEEN_SCENARIO);
			stmt.addBatch(REMOTE_CREATE_PROCEDURE_DUPLICATE_INPUT_FILES_BETWEEN_SCENARIO);
			stmt.addBatch(REMOTE_CREATE_PROCEDURE_DUPLICATE_ANALYSIS_FILES_BETWEEN_SCENARIO);
			stmt.addBatch(REMOTE_CREATE_PROCEDURE_DUPLICATE_ASSOCIATE_FILES_BETWEEN_SCENARIO);
			stmt.addBatch(REMOTE_CREATE_PROCEDURE_DUPLICATE_SCENARIO);
			stmt.addBatch(REMOTE_CREATE_PROCEDURE_DUPLICATE_SCENARIO_COLLECTION);
			stmt.addBatch(REMOTE_CREATE_PROCEDURE_PRINT_SCENARIO_PS_TO_XML);
			stmt.addBatch(REMOTE_CREATE_PROCEDURE_PRINT_SCENARIO_COLLECTION_TO_XML);
			stmt.addBatch(REMOTE_CREATE_PROCEDURE_PRINT_MODEL_VERSION_TO_XML);
			stmt.addBatch(REMOTE_CREATE_PROCEDURE_PRINT_MODEL_PROJECT_TO_XML);
			stmt.addBatch(REMOTE_CREATE_PROCEDURE_PRINT_GROUP_TO_XML);
			stmt.addBatch(REMOTE_CREATE_PROCEDURE_PRINT_USER_TO_XML);
			stmt.executeBatch();
			stmt.close();

		} catch (Exception e) {
			error = true;
			throw e;
		} finally {
			try {
				closeConnection(ds, conn, error);
			} catch (Exception e) {
				error = true;
				throw e;
			}
		}

		return !error;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.cs.usask.ca.silver.persistence.datasource.StorageI#clearStorage()
	 */
	public boolean clearStorage() throws Exception {

		Connection conn = null;
		Statement stmt = null;
		boolean error = false;

		try {
			conn = getConnection(ds);
			stmt = conn.createStatement();
			stmt.addBatch(DROP_PROCEDURE_PRINT_USER_TO_XML);
			stmt.addBatch(DROP_PROCEDURE_PRINT_GROUP_TO_XML);
			stmt.addBatch(DROP_PROCEDURE_PRINT_MODEL_PROJECT_TO_XML);
			stmt.addBatch(DROP_PROCEDURE_PRINT_MODEL_VERSION_TO_XML);
			stmt.addBatch(DROP_PROCEDURE_PRINT_SCENARIO_COLLECTION_TO_XML);
			stmt.addBatch(DROP_PROCEDURE_PRINT_SCENARIO_PS_TO_XML);
			stmt.addBatch(DROP_PROCEDURE_DUPLICATE_SCENARIO);
			stmt.addBatch(DROP_PROCEDURE_DUPLICATE_SCENARIO_COLLECTION);
			stmt.addBatch(DROP_PROCEDURE_DUPLICATE_ASSOCIATE_FILES_BETWEEN_SCENARIO);
			stmt.addBatch(DROP_PROCEDURE_DUPLICATE_ANALYSIS_FILES_BETWEEN_SCENARIO);
			stmt.addBatch(DROP_PROCEDURE_DUPLICATE_INPUT_FILES_BETWEEN_SCENARIO);
			stmt.addBatch(DROP_PROCEDURE_DUPLICATE_TRAJECTORY_FILES_BETWEEN_SCENARIO);
			stmt.addBatch(DROP_PROCEDURE_DUPLICATE_PARAMETERS_BETWEEN_PS);
			stmt.addBatch(DROP_PROCEDURE_DUPLICATE_PARAMETER);
			stmt.addBatch(DROP_PROCEDURE_SELECT_ALL_USER_IDS);
			stmt.addBatch(DROP_ADDUSERPRIVATEGROUPCONSTRAINT);
			stmt.addBatch(DROP_MVSOURCEFILECONSTRAINT);
			stmt.addBatch(DROP_MVBASELINESCENARIOCONSTRAINT);
			stmt.addBatch(DROP_MVORIGINALSCENARIOCONSTRAINT);
			stmt.addBatch(DROP_TABLE_U2Group);
			stmt.addBatch(DROP_TABLE_ANALYSISFILES);
			stmt.addBatch(DROP_TABLE_TRAJECTORYFILES);
			stmt.addBatch(DROP_TABLE_INPUTFILES);
			stmt.addBatch(DROP_TABLE_MP2F);
			stmt.addBatch(DROP_TABLE_MV2F);
			stmt.addBatch(DROP_TABLE_S2F);
			stmt.addBatch(DROP_TABLE_SC2F);
			stmt.addBatch(DROP_TABLE_FILE);
			stmt.addBatch(DROP_TABLE_SCENARIO);
			stmt.addBatch(DROP_TABLE_P2PS);
			stmt.addBatch(DROP_TABLE_PARAMETER);
			stmt.addBatch(DROP_TABLE_PARAMETERSET);
			stmt.addBatch(DROP_TABLE_SCENARIOCOLLECTION);
			stmt.addBatch(DROP_TABLE_MVERSION);
			stmt.addBatch(DROP_TABLE_MPROJECT);
			stmt.addBatch(DROP_TABLE_COMMENT);
			stmt.addBatch(DROP_TABLE_LOG_CHANGESET);
			stmt.addBatch(DROP_TABLE_GROUP);
			stmt.addBatch(DROP_TABLE_USER);
			stmt.executeBatch();
			stmt.close();

		} catch (Exception e) {
			error = true;
			throw e;
		} finally {
			try {
				closeConnection(ds, conn, error);
			} catch (Exception e) {
				error = true;
				throw e;
			}
		}
		return !error;
	}

	/* ----- SilverService Methods ---- */
	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#backupStorage()
	 */
	public boolean backupStorage(String destination) {
		// TODO: to be implemented with ad-hoc xml reprentation.
		return false;
	}

	/* ----- User Methods ----- */

	public ArrayList<UUID> getAllUserIDs() throws Exception {
		return executeSqlReturnUUIDArrayList(ds, USER_SELECT_ALLIDS);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.cs.usask.ca.silver.api.persistence.StorageI#selectFromUserProcedure()
	 */
	public ArrayList<UUID> getAllUserIDsThroughProcedure() throws Exception {
		String sqlStmt = "{ call SELECT_ALL_USER_IDS()}";
		return executeSqlReturnUUIDArrayList(ds, sqlStmt);
	}

	public UUID addUser(String uFirstName, String uLastName, String uEmail,
			String uPasswd) throws Exception {
		String sqlStmt;
		String uIdentity = getRandomUUID();
		String uFirstNameEnc = myBCodec.encode(uFirstName);
		String uLastNameEnc = myBCodec.encode(uLastName);
		String uEmailEnc = myBCodec.encode(uEmail);
		// TODO password encryption
		String uPasswdEnc = myBCodec.encode(uPasswd);
		sqlStmt = String.format(USER_INSERT, uIdentity, uFirstNameEnc,
				uLastNameEnc, uEmailEnc, uPasswdEnc);
		executeSqlReturnUUIDKey(ds, sqlStmt);
		return UUID.fromString(uIdentity);
	}

	public UUID addUserAndPrivateGroup(String uFirstName, String uLastName,
			String uEmail, String uPasswd, int privateGroupId) throws Exception {
		String sqlStmt;
		String uIdentity = myBCodec.encode(getRandomUUID());
		String uFirstNameEnc = myBCodec.encode(uFirstName);
		String uLastNameEnc = myBCodec.encode(uLastName);
		String uEmailEnc = myBCodec.encode(uEmail);
		// TODO password encryption
		String uPasswdEnc = myBCodec.encode(uPasswd);
		sqlStmt = String.format(USER_INSERT_WITH_PRIVATE_GROUP, uIdentity,
				uFirstNameEnc, uLastNameEnc, uEmailEnc, uPasswdEnc,
				privateGroupId);
		executeSqlReturnUUIDKey(ds, sqlStmt);
		return UUID.fromString(uIdentity);
	}

	public String getUserAsXML(UUID uId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(USER_SELECT, uId);
		return executeSqlSelectReturnXMLString(sqlStmt, ds);
	}

	public String[] getUserBasicInfoIntoStringArray(UUID uId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(USER_SELECT_BASIC_INFO, uId);
		return executeSqlReturnUserStringArray(5, ds, sqlStmt);
	}

	public String getUserFirstName(UUID uId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(USER_SELECT_FIRST_NAME, uId);
		return executeSqlReturnString(ds, sqlStmt);
	}

	public String getUserLastName(UUID uId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(USER_SELECT_LAST_NAME, uId);
		return executeSqlReturnString(ds, sqlStmt);
	}

	public String getUserEmail(UUID uId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(USER_SELECT_EMAIL, uId);
		return executeSqlReturnString(ds, sqlStmt);
	}

	public UUID getUserPrivateGroupId(UUID uId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(USER_SELECT_PRIVATE_GROUP, uId);
		return executeSqlReturnUUID(ds, sqlStmt);
	}

	public boolean verifyUserPassword(String email, String pass)
			throws Exception {
		String sqlStmt;
		String uEmailEnc = myBCodec.encode(email);
		sqlStmt = String.format(USER_SELECT_PASSWD_WITH_EMAIL, uEmailEnc);
		return executeSqlReturnString(ds, sqlStmt).compareTo(pass) == 0;
	}

	public Date getUserLastUpdatedTime(UUID uId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(USER_SELECT_LAST_UPDATE_TIME, uId);
		return executeSqlReturnTime(ds, sqlStmt);
	}

	public Date getUserRegistrationDate(UUID uId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(USER_SELECT_CREATIONDATE, uId);
		return executeSqlReturnDate(ds, sqlStmt);
	}

	public Date getUserRegistrationTime(UUID uId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(USER_SELECT_CREATIONDATE, uId);
		return executeSqlReturnTime(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.cs.usask.ca.silver.api.persistence.StorageI#
	 * getUserIdentityBasedOnEmailAndPassword(java.lang.String,
	 * java.lang.String)
	 */
	public UUID getUserIdentityBasedOnEmailAndPassword(String uEmail,
			String uPasswd) throws Exception {
		String sqlStmt;
		String uEmailEncoded = myBCodec.encode(uEmail);
		String uPassEncoded = myBCodec.encode(uPasswd);
		sqlStmt = String.format(USER_SELECT_IDENTITY_WITH_EMAIL_AND_PASS,
				uEmailEncoded, uPassEncoded);
		UUID result = executeSqlReturnUUID(ds, sqlStmt);

		// TODO remove
		if (result != null)
			logger.info(result.toString());

		return result;
	}

	public ArrayList<UUID> getUserParticipatedGroupIds(UUID uId)
			throws Exception {
		String sqlStmt;
		sqlStmt = String.format(USER_SELECT_PARTICIPATED_GROUP_IDS, uId);
		return executeSqlReturnUUIDArrayList(ds, sqlStmt);
	}

	public boolean setUserPrivateGroupId(UUID uId, UUID groupId)
			throws Exception {
		String sqlStmt;
		sqlStmt = String.format(USER_SET_PRIVATE_GROUP_ID, groupId, uId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	public boolean setUserFirstName(UUID uId, String updatedFirstName)
			throws Exception {
		String sqlStmt;
		String nameEnc = myBCodec.encode(updatedFirstName);
		sqlStmt = String.format(USER_SET_FIRST_NAME, nameEnc, uId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	public boolean setUserLastName(UUID uId, String updatedLastName)
			throws Exception {
		String sqlStmt;
		String nameEnc = myBCodec.encode(updatedLastName);
		sqlStmt = String.format(USER_SET_LAST_NAME, nameEnc, uId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	public boolean setUserEmail(UUID uId, String updatedEmail) throws Exception {
		String sqlStmt;
		String emailEnc = myBCodec.encode(updatedEmail);
		sqlStmt = String.format(USER_SET_EMAIL, emailEnc, uId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	public boolean setUserPasswd(UUID uId, String updatedPasswd)
			throws Exception {
		String sqlStmt;
		// TODO encrypt password
		String passwdEnc = myBCodec.encode(updatedPasswd);
		sqlStmt = String.format(USER_SET_PASSWD, passwdEnc, uId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	public boolean deleteUser(UUID uId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(USER_DELETE, uId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/* ----- Group Methods ----- */

	public UUID addGroup(String gName, UUID gOwnerId, String gPrivacy,
			String gDescription) throws Exception {
		String sqlStmt;
		String gIdentity = getRandomUUID();
		String gNameEnc = myBCodec.encode(gName);
		String gPrivacyEnc = myBCodec.encode(gPrivacy);
		String gDescriptionEnc = myBCodec.encode(gDescription);
		sqlStmt = String.format(GROUP_INSERT, gIdentity, gNameEnc,
				gOwnerId.toString(), gPrivacyEnc, gDescriptionEnc);
		executeSqlReturnUUIDKey(ds, sqlStmt);
		return UUID.fromString(gIdentity);
	}

	public ArrayList<UUID> getAllGroupIDs() throws Exception {
		return executeSqlReturnUUIDArrayList(ds, GROUP_SELECT_ALLIDS);
	}

	public ArrayList<String> getAllGroupNames() throws Exception {
		return executeSqlReturnDecodedStringArrayList(ds, GROUP_SELECT_ALLNAMES);
	}

	public String getGroupName(UUID gId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(GROUP_SELECT_NAME, gId);
		return executeSqlReturnString(ds, sqlStmt);
	}

	public HashMap<UUID, String> getAllGroupIDNameAsHashedMap()
			throws Exception {
		return executeSqlReturnUUIDStringHashMap(
				GROUP_SELECT_ALL_GROUP_ID_AND_GROUP_NAME_AS_SET_OF_PAIRS, ds);
	}

	public UUID getGroupOwnerID(UUID gId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(GROUP_SELECT_OWNER_ID, gId);
		return executeSqlReturnUUID(ds, sqlStmt);
	}

	public String getGroupOwnerName(UUID gId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(GROUP_SELECT_OWNER_NAME, gId);
		return executeSqlReturnString(ds, sqlStmt);
	}

	public String getGroupPrivacy(UUID gId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(GROUP_SELECT_PRIVACY, gId);
		return executeSqlReturnString(ds, sqlStmt);
	}

	public String getGroupDescription(UUID gId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(GROUP_SELECT_DESCRIPTION, gId);
		return executeSqlReturnString(ds, sqlStmt);
	}

	public Date getGroupUpdateTime(UUID gId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(GROUP_SELECT_LAST_UPDATE_TIME, gId);
		return executeSqlReturnTime(ds, sqlStmt);
	}

	public Date getGroupCreationDate(UUID gId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(GROUP_SELECT_CREATIONDATE, gId);
		return executeSqlReturnDate(ds, sqlStmt);
	}

	public ArrayList<UUID> getGroupContainingModelProjectIDs(UUID gId)
			throws Exception {
		String sqlStmt;
		sqlStmt = String.format(GROUP_SELECT_MPROJECT_IDS, gId);
		return executeSqlReturnUUIDArrayList(ds, sqlStmt);
	}

	public ArrayList<UUID> getGroupContainingUserIDs(UUID gId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(GROUP_SELECT_ALL_USER_IDS_WITHIN_THIS_GROUP,
				gId);
		return executeSqlReturnUUIDArrayList(ds, sqlStmt);
	}

	public boolean setGroupName(UUID gId, String updatedName) throws Exception {
		String sqlStmt;
		String updatedNameEnc = myBCodec.encode(updatedName);
		sqlStmt = String.format(GROUP_SET_NAME, updatedNameEnc, gId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	public boolean setGroupPrivacy(UUID gId, String updatedPrivacy)
			throws Exception {
		String sqlStmt;
		String updatedPrivacyEnc = myBCodec.encode(updatedPrivacy);
		sqlStmt = String.format(GROUP_SET_PRIVACY, updatedPrivacyEnc, gId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	public boolean setGroupOwnerID(UUID gId, UUID updatedOwnerId)
			throws Exception {
		String sqlStmt;
		sqlStmt = String.format(GROUP_SET_OWNER_ID, updatedOwnerId, gId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	public boolean setGroupDescription(UUID gId, String updatedGroupDescription)
			throws Exception {
		String sqlStmt;
		String updatedGroupDescriptionEnc = myBCodec
				.encode(updatedGroupDescription);
		sqlStmt = String.format(GROUP_SET_DESCRIPTION,
				updatedGroupDescriptionEnc, gId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	public boolean deleteGroup(UUID gId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(GROUP_DELETE, gId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	public boolean addUserToGroup(UUID uId, UUID gId) throws Exception {

		String sqlStmt;
		sqlStmt = String.format(USER_PARTICIPATES_GROUP, uId, gId);
		return executeSqlReturnBoolean(ds, sqlStmt);

	}

	public boolean removeUserFromGroup(UUID uId, UUID gId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(GROUP_REMOVE_USERS_FROM_THIS_GROUP, uId, gId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	public ArrayList<UUID> getAllUsersWithinThisGroup(UUID uId)
			throws Exception {
		return executeSqlReturnUUIDArrayList(ds,
				GROUP_SELECT_ALL_USER_IDS_WITHIN_THIS_GROUP);
	}

	/* ----- Model Project Methods ---- */

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#getAllModelProjectIDs()
	 */
	public ArrayList<UUID> getAllModelProjectIDs() throws Exception {
		return executeSqlReturnUUIDArrayList(ds, MODEL_PROJECT_SELECT_ALLIDS);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#addModelProject(java.lang.String,
	 * java.lang.String, java.lang.String)
	 */
	public UUID addModelProject(UUID groupId, String mpName, String mpCreator,
			String mpType, String mpDescr) throws Exception {
		String sqlStmt;
		String mpIdentity = getRandomUUID();
		String mpVersion = getRandomUUID();
		String mpNameEnc = myBCodec.encode(mpName);
		String mpCreatorEnc = myBCodec.encode(mpCreator);
		String mpTypeEnc = myBCodec.encode(mpType);
		String mpDescrEnc = myBCodec.encode(mpDescr);
		sqlStmt = String.format(MODEL_PROJECT_INSERT, mpIdentity, mpVersion,
				groupId, mpNameEnc, mpCreatorEnc, mpTypeEnc, mpDescrEnc);
		executeSqlReturnUUIDKey(ds, sqlStmt);
		return UUID.fromString(mpIdentity);
	}

	public String[] getModelProjectNamesByGroupId(UUID gId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(MODEL_PROJECT_SELECT_NAME_BY_PARENT_GROUP_ID,
				gId);
		ArrayList<String> names = executeSqlReturnDecodedStringArrayList(ds,
				sqlStmt);

		return names.toArray(new String[0]);

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#getModelProjectAsXML(int)
	 */
	public String getModelProjectAsXML(UUID mpId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(MODEL_PROJECT_SELECT, mpId);
		return executeSqlSelectReturnXMLString(sqlStmt, ds);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#getMpName(int)
	 */
	public String getMpName(UUID mpId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(MODEL_PROJECT_SELECT_NAME, mpId);
		return executeSqlReturnString(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.cs.usask.ca.silver.api.persistence.StorageI#getMpParentGroup(java
	 * .util.UUID)
	 */
	public UUID getMpParentGroup(UUID mpId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(MODEL_PROJECT_SELECT_GROUP_ID, mpId);
		return executeSqlReturnUUID(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.cs.usask.ca.silver.api.persistence.StorageI#getMpVersion(int)
	 */
	public UUID getMpVersion(UUID mpId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(MODEL_PROJECT_SELECT_VERSION_IDENTITY, mpId);
		return executeSqlReturnUUID(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.cs.usask.ca.silver.api.persistence.StorageI#getMpLastUpdateTime(int)
	 */
	public Date getMpLastUpdateTime(UUID mpId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(MODEL_PROJECT_SELECT_UPDATE_TIME, mpId);
		return executeSqlReturnTime(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#getMpDescription(int)
	 */
	public String getMpDescription(UUID mpId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(MODEL_PROJECT_SELECT_DESCRIPTION, mpId);
		return executeSqlReturnString(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#getMpCreator(int)
	 */
	public String getMpCreator(UUID mpId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(MODEL_PROJECT_SELECT_CREATOR, mpId);
		return executeSqlReturnString(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#getMpCreationDate(int)
	 */
	public Date getMpCreationDate(UUID mpId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(MODEL_PROJECT_SELECT_CREATIONDATE, mpId);
		return executeSqlReturnDate(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silvercore.persistence.Storage#getMpCreationTime(int)
	 */
	public Date getMpCreationTime(UUID mpId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(MODEL_PROJECT_SELECT_CREATIONDATE, mpId);
		return executeSqlReturnTime(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silvercore.persistence.Storage#getMpType(int)
	 */
	public String getMpType(UUID mpId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(MODEL_PROJECT_SELECT_TYPE, mpId);
		return executeSqlReturnString(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#getMpModelVersionIds(int)
	 */
	public ArrayList<UUID> getMpModelVersionIds(UUID mpId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(MODEL_PROJECT_SELECT_MVERSIONIDS, mpId);
		return executeSqlReturnUUIDArrayList(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#getMpModelVersions(int)
	 */
	public HashMap<UUID, String> getMpModelVersions(UUID mpId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(MODEL_PROJECT_SELECT_MVERSIONIDANDNAME, mpId);
		return executeSqlReturnUUIDStringHashMap(sqlStmt, ds);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#getMpMostRecentModelVersionId
	 * (int)
	 */
	// public int getMpMostRecentModelVersionId(int mpId) throws Exception {
	// String sqlStmt;
	// sqlStmt = String
	// .format(MODEL_PROJECT_SELECT_MOSTRECENTMVERSIONID, mpId);
	// return executeSqlReturnInt(sqlStmt);
	// }

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silvercore.persistence.Storage#getModelProjectAssociatedFileIds
	 * (int)
	 */
	public ArrayList<UUID> getModelProjectAssociatedFileIds(UUID mpId)
			throws Exception {
		String sqlStmt;
		sqlStmt = String.format(MODEL_PROJECT_SELECT_ASSOCIATEDFILEIDS, mpId);
		return executeSqlReturnUUIDArrayList(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#setMpName(int,
	 * java.lang.String)
	 */
	public boolean setMpName(UUID mpId, String name) throws Exception {
		String sqlStmt;
		String nameEnc = myBCodec.encode(name);
		sqlStmt = String.format(MODEL_PROJECT_SET_NAME, nameEnc, mpId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.cs.usask.ca.silver.api.persistence.StorageI#setMpVersion(int,
	 * java.util.UUID)
	 */
	public boolean setMpVersion(UUID mpId, UUID version) throws Exception {
		String sqlStmt;
		// verify the identity is of proper uuid format
		String versionEnc = myBCodec.encode(version.toString());
		sqlStmt = String.format(MODEL_PROJECT_SET_VERSION, versionEnc, mpId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#setMpDescription(int,
	 * java.lang.String)
	 */
	public boolean setMpDescription(UUID mpId, String description)
			throws Exception {
		String sqlStmt;
		String descEnc = myBCodec.encode(description);
		sqlStmt = String.format(MODEL_PROJECT_SET_DESCRIPTION, descEnc, mpId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silvercore.persistence.Storage#setMpType(int,
	 * java.lang.String)
	 */
	public boolean setMpType(UUID mpId, String mpType) throws Exception {
		String sqlStmt;
		String typeEnc = myBCodec.encode(mpType);
		sqlStmt = String.format(MODEL_PROJECT_SET_TYPE, typeEnc, mpId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#deleteModelProject(int)
	 */
	public boolean deleteModelProject(UUID mpId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(MODEL_PROJECT_DELETE, mpId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/* ----- Model Version Methods ---- */
	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#addModelVersion(java.lang.String,
	 * java.lang.String, java.lang.String, int, int)
	 */
	public UUID addModelVersion(String vName, String vCreator, String vType,
			String vDescr, UUID mpIdentity, int visibility) throws Exception {
		String sqlStmt;
		String vIdentity = getRandomUUID();
		String vVersion = getRandomUUID();
		String vNameEnc = myBCodec.encode(vName);
		String vCreatorEnc = myBCodec.encode(vCreator);
		String vTypeEnc = myBCodec.encode(vType);
		String vDescrEnc = myBCodec.encode(vDescr);

		sqlStmt = String.format(MODEL_VERSION_INSERT, vIdentity, vVersion,
				vNameEnc, vCreatorEnc, vTypeEnc, vDescrEnc, mpIdentity,
				visibility);

		executeSqlReturnUUIDKey(ds, sqlStmt);

		return UUID.fromString(vIdentity);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#getModelVersionAsXML(int)
	 */
	public String getModelVersionAsXML(UUID mvId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(MODEL_VERSION_SELECT, mvId);
		return executeSqlSelectReturnXMLString(sqlStmt, ds);
	}

	public UUID getMvVersion(UUID mvId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(MODEL_VERSION_SELECT_VERSION, mvId);
		return executeSqlReturnUUID(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.cs.usask.ca.silver.api.persistence.StorageI#getMvUpdateTime(java.
	 * util.UUID)
	 */
	public Date getMvUpdateTime(UUID mvId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(MODEL_VERSION_SELECT_LAST_UPDATE_TIME, mvId);
		return executeSqlReturnTime(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#getMvName(int)
	 */
	public String getMvName(UUID mvId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(MODEL_VERSION_SELECT_NAME, mvId);
		return executeSqlReturnString(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silvercore.persistence.Storage#getMvType(int)
	 */
	public String getMvType(UUID mvId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(MODEL_VERSION_SELECT_TYPE, mvId);
		return executeSqlReturnString(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#getMvDescription(int)
	 */
	public String getMvDescription(UUID mvId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(MODEL_VERSION_SELECT_DESCRIPTION, mvId);
		return executeSqlReturnString(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#getMvCreator(int)
	 */
	public String getMvCreator(UUID mvId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(MODEL_VERSION_SELECT_CREATOR, mvId);
		return executeSqlReturnString(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#getMvCreationDate(int)
	 */
	public Date getMvCreationDate(UUID mvId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(MODEL_VERSION_SELECT_CREATIONDATE, mvId);
		return executeSqlReturnDate(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silvercore.persistence.Storage#getMvCreationTime(int)
	 */
	public Date getMvCreationTime(UUID mvId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(MODEL_VERSION_SELECT_CREATIONDATE, mvId);
		return executeSqlReturnTime(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#getMvParentProjectId(int)
	 */
	public UUID getMvParentProjectId(UUID mvId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(MODEL_VERSION_SELECT_MPID, mvId);
		return executeSqlReturnUUID(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#getMvSourceFileId(int)
	 */
	public UUID getMvSourceFileId(UUID mvId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(MODEL_VERSION_SELECT_SOURCEFILE, mvId);
		return executeSqlReturnUUID(ds, sqlStmt);
	}

	public String getMvSourceFileName(UUID mvId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(MODEL_VERSION_SELECT_SOURCEFILE_NAME, mvId);
		return executeSqlReturnString(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#getMvOriginalScenarioId(int)
	 */
	public UUID getMvOriginalScenarioId(UUID mvId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(MODEL_VERSION_SELECT_ORIGINALS, mvId);
		return executeSqlReturnUUID(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#getMvBaselineScenarioId(int)
	 */
	public UUID getMvBaselineScenarioId(UUID mvId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(MODEL_VERSION_SELECT_BASELINES, mvId);
		return executeSqlReturnUUID(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#getMvMostRecentScenarioId(int)
	 */
	public UUID getMvMostRecentScenarioId(UUID mvId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(MODEL_VERSION_SELECT_MOSTRECENTSID, mvId);
		return executeSqlReturnUUID(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#getMvScenarioCollectionIds(int)
	 */
	public ArrayList<UUID> getMvScenarioCollectionIds(UUID mvId)
			throws Exception {
		String sqlStmt;
		sqlStmt = String.format(MODEL_VERSION_SELECT_SCIDS, mvId);
		return executeSqlReturnUUIDArrayList(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#getMvParameterSetCollections(int)
	 */
	public HashMap<UUID, String> getMvParameterSetCollections(UUID mvId)
			throws Exception {
		String sqlStmt;
		sqlStmt = String.format(MODEL_VERSION_SELECT_SCIDANDNAME, mvId);
		return executeSqlReturnUUIDStringHashMap(sqlStmt, ds);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#getMvMostRecentScenarioCollectionId
	 * (int)
	 */
	// public int getMvMostRecentScenarioCollectionId(int mvId) throws Exception
	// {
	// String sqlStmt;
	// sqlStmt = String.format(MODEL_VERSION_SELECT_MOSTRECENTSCID, mvId);
	// return executeSqlReturnInt(sqlStmt);
	// }

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silvercore.persistence.Storage#getModelVersionAssociatedFileIds
	 * (int)
	 */
	public ArrayList<UUID> getModelVersionAssociatedFileIds(UUID mvId)
			throws Exception {
		String sqlStmt;
		sqlStmt = String.format(MODEL_VERSION_SELECT_ASSOCIATEDFILEIDS, mvId);
		return executeSqlReturnUUIDArrayList(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.cs.usask.ca.silver.api.persistence.StorageI#setMvVersion(int,
	 * java.util.UUID)
	 */
	public boolean setMvVersion(UUID mvId, UUID version) throws Exception {
		String sqlStmt;
		// validify version in UUID
		sqlStmt = String.format(MODEL_VERSION_SET_VERSION, version, mvId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#setMvName(int,
	 * java.lang.String)
	 */
	public boolean setMvName(UUID mvId, String name) throws Exception {
		String sqlStmt;
		String nameEnc = myBCodec.encode(name);
		sqlStmt = String.format(MODEL_VERSION_SET_NAME, nameEnc, mvId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.cs.usask.ca.silver.api.persistence.StorageI#setMvCreator(java.util
	 * .UUID, java.lang.String)
	 */
	public boolean setMvCreator(UUID mvId, String creator) throws Exception {
		String sqlStmt;
		String creatorEnc = myBCodec.encode(creator);
		sqlStmt = String.format(MODEL_VERSION_SET_CREATOR, creatorEnc, mvId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silvercore.persistence.Storage#setMvType(int,
	 * java.lang.String)
	 */
	public boolean setMvType(UUID mvId, String type) throws Exception {
		String sqlStmt;
		String typeEnc = myBCodec.encode(type);
		sqlStmt = String.format(MODEL_VERSION_SET_TYPE, typeEnc, mvId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#setMvDescription(int,
	 * java.lang.String)
	 */
	public boolean setMvDescription(UUID mvId, String description)
			throws Exception {
		String sqlStmt;
		String descEnc = myBCodec.encode(description);
		sqlStmt = String.format(MODEL_VERSION_SET_DESCRIPTION, descEnc, mvId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#setMvBaselineScenario(int,
	 * int)
	 */
	public boolean setMvBaselineScenario(UUID mvId, UUID sId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(MODEL_VERSION_SET_BASELINES, sId, mvId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#setMvOriginalScenario(int,
	 * int)
	 */
	public boolean setMvOriginalScenario(UUID mvId, UUID sId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(MODEL_VERSION_SET_ORIGINALS, sId, mvId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#setMvSourceFileId(int, int)
	 */
	public boolean setMvSourceFileId(UUID mvId, UUID fId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(MODEL_VERSION_SET_SOURCEFILEID, fId, mvId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#deleteVersion(int)
	 */
	public boolean deleteVersion(UUID mvId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(MODEL_VERSION_DELETE, mvId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/* ----- Scenario Collection Methods ---- */
	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#addScenarioCollection(java.lang
	 * .String, java.lang.String, java.lang.String, int)
	 */
	public UUID addScenarioCollection(String scName, String scCreator,
			String scDescr, UUID mvId) throws Exception {
		String sqlStmt;
		String scIdentity = getRandomUUID();
		String scVersion = getRandomUUID();
		String scNameEnc = myBCodec.encode(scName);
		String scCreatorEnc = myBCodec.encode(scCreator);
		String scDescrEnc = myBCodec.encode(scDescr);
		sqlStmt = String.format(SCOLLECTION_INSERT, scIdentity, scVersion,
				scNameEnc, scCreatorEnc, scDescrEnc, mvId);
		executeSqlReturnUUIDKey(ds, sqlStmt);
		return UUID.fromString(scIdentity);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#getScenarioCollectionAsXML(int)
	 */
	public String getScenarioCollectionAsXML(UUID scId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCOLLECTION_SELECT, scId);
		return executeSqlSelectReturnXMLString(sqlStmt, ds);
	}

	public UUID getScenarioCollectionIdentity(UUID scId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCOLLECTION_SELECT_IDENTITY, scId);
		String result = executeSqlReturnString(ds, sqlStmt);
		return UUID.fromString(result);
	}

	public UUID getScenarioCollectionVersion(UUID scId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCOLLECTION_SELECT_VERSION, scId);
		return executeSqlReturnUUID(ds, sqlStmt);
	}

	public Date getScenarioCollectionUpdateTime(UUID scId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCOLLECTION_SELECT_LAST_UPDATE_TIME, scId);
		return executeSqlReturnTime(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#getScenarioCollectionName(int)
	 */
	public String getScenarioCollectionName(UUID scId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCOLLECTION_SELECT_NAME, scId);
		return executeSqlReturnString(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#getScenarioCollectionDescription
	 * (int)
	 */
	public String getScenarioCollectionDescription(UUID scId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCOLLECTION_SELECT_DESCRIPTION, scId);
		return executeSqlReturnString(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#getScenarioCollectionCreator(int)
	 */
	public String getScenarioCollectionCreator(UUID scId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCOLLECTION_SELECT_CREATOR, scId);
		return executeSqlReturnString(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#getScenarioCollectionCreationDate
	 * (int)
	 */
	public Date getScenarioCollectionCreationDate(UUID scId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCOLLECTION_SELECT_CREATIONDATE, scId);
		return executeSqlReturnDate(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silvercore.persistence.Storage#getScenarioCollectionCreationTime
	 * (int)
	 */
	public Date getScenarioCollectionCreationTime(UUID scId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCOLLECTION_SELECT_CREATIONDATE, scId);
		return executeSqlReturnTime(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#getScenarioCollectionParentVersionId
	 * (int)
	 */
	public UUID getScenarioCollectionParentVersionId(UUID scId)
			throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCOLLECTION_SELECT_MVERSIONID, scId);
		return executeSqlReturnUUID(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#getScenarioCollectionScenarioIds
	 * (int)
	 */
	public ArrayList<UUID> getScenarioCollectionScenarioIds(UUID scId)
			throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCOLLECTION_SELECT_SCENARIOIDS, scId);
		return executeSqlReturnUUIDArrayList(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @seeedu.usask.cs.silver.persistence.Storage#
	 * getScenarioCollectionScenarioIdsAndNames(int)
	 */
	public HashMap<UUID, String> getScenarioCollectionScenarioIdsAndNames(
			UUID scId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCOLLECTION_SELECT_SCENARIOIDSANDNAMES, scId);
		return executeSqlReturnUUIDStringHashMap(sqlStmt, ds);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @seeedu.usask.cs.silver.persistence.Storage#
	 * getScenarioCollectionMostRecentScenarioId(int)
	 */
	public UUID getScenarioCollectionMostRecentScenarioId(UUID scId)
			throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCOLLECTION_SELECT_MOSTRECENTSCENARIOID, scId);
		return executeSqlReturnUUID(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @seeedu.usask.cs.silver.persistence.Storage#
	 * getScenarioCollectionAssociatedFileIds(int)
	 */
	public ArrayList<UUID> getScenarioCollectionAssociatedFileIds(UUID scId)
			throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCOLLECTION_SELECT_ASSOCIATEDFILEIDS, scId);
		return executeSqlReturnUUIDArrayList(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.cs.usask.ca.silver.api.persistence.StorageI#
	 * getScenarioCollectionAssociatedFileNames(java.util.UUID)
	 */
	public ArrayList<String> getScenarioCollectionAssociatedFileNames(UUID scId)
			throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCOLLECTION_SELECT_ASSOCIATEDFILE_NAMES, scId);
		return executeSqlReturnDecodedStringArrayList(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#setScenarioCollectionName(int,
	 * java.lang.String)
	 */
	public boolean setScenarioCollectionName(UUID scId, String name)
			throws Exception {
		String sqlStmt;
		String nameEnc = myBCodec.encode(name);
		sqlStmt = String.format(SCOLLECTION_SET_NAME, nameEnc, scId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#setScenarioCollectionDescr(int,
	 * java.lang.String)
	 */
	public boolean setScenarioCollectionDescr(UUID scId, String description)
			throws Exception {
		String sqlStmt;
		String descEnc = myBCodec.encode(description);
		sqlStmt = String.format(SCOLLECTION_SET_DESCRIPTION, descEnc, scId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.cs.usask.ca.silver.api.persistence.StorageI#setScenarioCollectionVersion
	 * (java.util.UUID, java.util.UUID)
	 */
	public boolean setScenarioCollectionVersion(UUID scId, UUID updatedVersion)
			throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCOLLECTION_SET_VERSION, updatedVersion, scId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.cs.usask.ca.silver.api.persistence.StorageI#setSceanrioCollectionCreator
	 * (java.util.UUID, java.lang.String)
	 */
	public boolean setScenarioCollectionCreator(UUID scId, String updateCreator)
			throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCOLLECTION_SET_CREATOR,
				myBCodec.encode(updateCreator), scId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#deleteScenarioCollection(int)
	 */
	public boolean deleteScenarioCollection(UUID scId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCOLLECTION_DELETE, scId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.cs.usask.ca.silver.api.persistence.StorageI#
	 * deleteAllScenarioCollectionAssociatedFiles(java.util.UUID)
	 */
	public boolean deleteAllScenarioCollectionAssociatedFiles(UUID scId)
			throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCOLLECTION_DELETE_ASSOCIATEDFILEIDS, scId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/* ----- Scenario Methods ---- */
	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#addScenario(java.lang.String,
	 * java.lang.String, java.lang.String, int, int, int)
	 */
	public UUID addScenario(String sName, String sCreator, String sType,
			String sDescr, UUID mvId, UUID scId, int psId) throws Exception {
		String sqlStmt;
		String sIdentity = getRandomUUID();
		String sVersion = getRandomUUID();
		String sNameEnc = myBCodec.encode(sName);
		String sCreatorEnc = myBCodec.encode(sCreator);
		String sTypeEnc = myBCodec.encode(sType);
		String sDescrEnc = myBCodec.encode(sDescr);
		sqlStmt = String.format(SCENARIO_INSERT, sIdentity, sVersion, sNameEnc,
				sCreatorEnc, sTypeEnc, sDescrEnc, mvId, scId, psId);
		executeSqlReturnUUIDKey(ds, sqlStmt);
		return UUID.fromString(sIdentity);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#getScenarioAsXML(int)
	 */
	public String getScenarioAsXML(UUID sId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCENARIO_SELECT, sId);
		return executeSqlSelectReturnXMLString(sqlStmt, ds);
	}

	public UUID getScenarioIdentity(UUID sId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCENARIO_SELECT_IDENTITY, sId);
		return UUID.fromString(executeSqlReturnString(ds, sqlStmt));
	}

	public UUID getScenarioVersion(UUID sId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCENARIO_SELECT_VERSION, sId);
		return executeSqlReturnUUID(ds, sqlStmt);
	}

	public Date getScenarioUpdateTime(UUID sId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCENARIO_SELECT_LAST_UPDATE_TIME, sId);
		return executeSqlReturnTime(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#getScenarioName(int)
	 */
	public String getScenarioName(UUID sId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCENARIO_SELECT_NAME, sId);
		return executeSqlReturnString(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silvercore.persistence.Storage#getScenarioType(int)
	 */
	public String getScenarioType(UUID sId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCENARIO_SELECT_TYPE, sId);
		return executeSqlReturnString(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#getScenarioDescription(int)
	 */
	public String getScenarioDescription(UUID sId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCENARIO_SELECT_DESCRIPTION, sId);
		return executeSqlReturnString(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#getScenarioCreator(int)
	 */
	public String getScenarioCreator(UUID sId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCENARIO_SELECT_CREATOR, sId);
		return executeSqlReturnString(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silvercore.persistence.Storage#getScenarioAssociatedFileIds
	 * (int)
	 */
	public ArrayList<UUID> getScenarioAssociatedFileIds(UUID sId)
			throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCENARIO_SELECT_ASSOCIATEDFILEIDS, sId);
		return executeSqlReturnUUIDArrayList(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#getScenarioCreationDate(int)
	 */
	public Date getScenarioCreationDate(UUID sId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCENARIO_SELECT_CREATIONDATE, sId);
		return executeSqlReturnDate(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silvercore.persistence.Storage#getScenarioCreationTime(int)
	 */
	public Date getScenarioCreationTime(UUID sId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCENARIO_SELECT_CREATIONDATE, sId);
		return executeSqlReturnTime(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#getScenarioParentVersionId(int)
	 */
	public UUID getScenarioParentVersionId(UUID sId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCENARIO_SELECT_MVERSIONID, sId);
		return executeSqlReturnUUID(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.cs.usask.ca.silver.api.persistence.StorageI#
	 * getScenarioParentVersionSimulationType(java.util.UUID)
	 */
	public String getScenarioParentVersionSimulationType(UUID sId)
			throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCENARIO_SELECT_MVERSION_TYPE, sId);
		return executeSqlReturnString(ds, sqlStmt);
	}

	public boolean isBaselineScenario(UUID sId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(
				SCENARIO_SELECT_PARENT_MVERSION_BASELINE_SCENARIOID, sId);
		UUID resultSid = executeSqlReturnUUID(ds, sqlStmt);
		return sId.equals(resultSid);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#getScenarioParentScenarioCollectionId
	 * (int)
	 */
	public UUID getScenarioParentScenarioCollectionId(UUID sId)
			throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCENARIO_SELECT_SCID, sId);
		return executeSqlReturnUUID(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#getScenarioParameterSetId(int)
	 */
	public int getScenarioParameterSetId(UUID sId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCENARIO_SELECT_PSID, sId);
		return executeSqlReturnInt(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#getScenarioRunStartTime(int)
	 */
	public Date getScenarioRunStartTime(UUID sId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCENARIO_SELECT_RUNSTARTTIME, sId);
		return executeSqlReturnDate(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#getScenarioRunEndTime(int)
	 */
	public Date getScenarioRunEndTime(UUID sId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCENARIO_SELECT_RUNENDTIME, sId);
		return executeSqlReturnDate(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#getScenarioRunUser(int)
	 */
	public String getScenarioRunUser(UUID sId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCENARIO_SELECT_RUNUSER, sId);
		return executeSqlReturnString(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#getScenarioInputFileIds(int)
	 */
	public ArrayList<UUID> getScenarioInputFileIds(UUID sId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCENARIO_SELECT_INPUTFILEIDS, sId);
		return executeSqlReturnUUIDArrayList(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.cs.usask.ca.silver.api.persistence.StorageI#getScenarioFirstInputFileId
	 * (java.util.UUID)
	 */
	public UUID getScenarioFirstInputFileId(UUID sId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCENARIO_SELECT_INPUTFILEIDS, sId);
		return executeMultipleOrSingleRowSqlReturnFirstUUID(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.cs.usask.ca.silver.api.persistence.StorageI#getScenarioFirstInputFileName
	 * (java.util.UUID)
	 */
	public String getScenarioFirstInputFileName(UUID sId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCENARIO_SELECT_INPUTFILENAMES, sId);
		return executeMultipleOrSingleSqlReturnFirstString(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#getScenarioTrajectoryFileIds(int)
	 */
	public ArrayList<UUID> getScenarioTrajectoryFileIds(UUID sId)
			throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCENARIO_SELECT_TRAJECTORYFILEIDS, sId);
		return executeSqlReturnUUIDArrayList(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#getScenarioAnalysisFileIds(int)
	 */
	public ArrayList<UUID> getScenarioAnalysisFileIds(UUID sId)
			throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCENARIO_SELECT_ANALYSISFILEIDS, sId);
		return executeSqlReturnUUIDArrayList(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#setScenarioName(int,
	 * java.lang.String)
	 */
	public boolean setScenarioName(UUID sId, String name) throws Exception {
		String sqlStmt;
		String nameEnc = myBCodec.encode(name);
		sqlStmt = String.format(SCENARIO_SET_NAME, nameEnc, sId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.cs.usask.ca.silver.api.persistence.StorageI#setScenarioVersion(java
	 * .util.UUID, java.util.UUID)
	 */
	public boolean setScenarioVersion(UUID sId, UUID version) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCENARIO_SET_VERSION, version, sId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	@Override
	public boolean setScenarioAsBaselineScenarioToParentVersion(UUID sId)
			throws Exception {
		String sqlStmt;
		sqlStmt = String.format(
				SCENARIO_SET_PARENT_MVERSION_BASELINE_SCENARIOID, sId, sId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silvercore.persistence.Storage#setScenarioType(int,
	 * java.lang.String)
	 */
	public boolean setScenarioType(UUID sId, String sType) throws Exception {
		String sqlStmt;
		String typeEnc = myBCodec.encode(sType);
		sqlStmt = String.format(SCENARIO_SET_TYPE, typeEnc, sId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	public boolean setScenarioCreator(UUID sId, String sCreator)
			throws Exception {
		String sqlStmt;
		String creatorEnc = myBCodec.encode(sCreator);
		sqlStmt = String.format(SCENARIO_SET_CREATOR, creatorEnc, sId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#setScenarioDescription(int,
	 * java.lang.String)
	 */
	public boolean setScenarioDescription(UUID sId, String description)
			throws Exception {
		String sqlStmt;
		String descEnc = myBCodec.encode(description);
		sqlStmt = String.format(SCENARIO_SET_DESCRIPTION, descEnc, sId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#setScenarioScenarioCollectionId
	 * (int, int)
	 */
	public boolean setScenarioScenarioCollectionId(UUID sId, UUID scId)
			throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCENARIO_SET_SC, scId, sId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#setScenarioRunUser(int,
	 * java.lang.String)
	 */
	public boolean setScenarioRunUser(UUID sId, String user) throws Exception {
		String sqlStmt;
		String userEnc = myBCodec.encode(user);
		sqlStmt = String.format(SCENARIO_SET_RUNUSER, userEnc, sId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#setScenarioRunStartTime(int)
	 */
	public boolean setScenarioRunStartTime(UUID sId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCENARIO_SET_RUNSTARTTIME, sId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#setScenarioRunEndTime(int)
	 */
	public boolean setScenarioRunEndTime(UUID sId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCENARIO_SET_RUNENDTIME, sId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#deleteScenario(int)
	 */
	public boolean deleteScenario(UUID sId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(SCENARIO_DELETE, sId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/*
	 * ==== Methods to Satisfy requests from ScenarioCollection regarding
	 * ParameterSets ====
	 */
	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#addParameterSet(java.lang.String)
	 */
	public int addParameterSet(String description) throws Exception {
		String sqlStmt;
		String descEnc = myBCodec.encode(description);
		sqlStmt = String.format(PARAMETERSET_INSERT, descEnc);
		return executeSqlReturnIntKey(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#getParameterSetParametersAsDom
	 * (int) Used: http://www.developer.com/db/article.php/10920_3329001_1
	 */
	public Document getParameterSetParametersAsDom(int psId) throws Exception {
		Connection conn = null;
		Statement stmt = null;
		ArrayList<Integer> pIds = getParameterSetParameterIds(psId);
		String sqlStmt;
		boolean error = false;
		// ////// turn results set into DOM
		DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
		DocumentBuilder builder = factory.newDocumentBuilder();
		Document returnDoc = builder.newDocument();

		Element results = returnDoc.createElement("PARAMETERSET");
		returnDoc.appendChild(results);

		// Create Connection and execute SQL
		try {

			// Iterate through all parameters
			for (int j = 0; j < pIds.size(); j++) {
				conn = getConnection(ds);

				stmt = conn.createStatement();
				sqlStmt = String.format(PARAMETERSET_SELECTALLPARAMETERS,
						pIds.get(j), psId, pIds.get(j));
				ResultSet rs = stmt.executeQuery(sqlStmt);

				// TODO - Check for empty resultset ... following doesn't work
				// because the cursor can't be reset
				// if (!rs.next())
				// return null;
				// rs.beforeFirst(); // Reset the resultSet cursor

				ResultSetMetaData rsmd = rs.getMetaData();
				int colCount = rsmd.getColumnCount();
				while (rs.next()) {

					Element row = returnDoc.createElement(rsmd.getTableName(1));
					results.appendChild(row);

					for (int i = 1; i <= colCount; i++) {
						String columnName = rsmd.getColumnName(i);
						Object value = rs.getObject(i);
						Element node = returnDoc.createElement(columnName);
						if (rsmd.getColumnType(i) == java.sql.Types.VARCHAR) // decode
							// strings
							node.appendChild(returnDoc.createTextNode(myBCodec
									.decode(value.toString())));
						else
							node.appendChild(returnDoc.createTextNode(value
									.toString()));
						row.appendChild(node);
					}
				}

				// Close statement and Connection
				stmt.close();
			}

		} catch (SQLException sqlExcept) {

			error = true;
			throw new Exception(sqlExcept.getMessage());
		} catch (Exception except) {

			error = true;
			throw except;
		} finally {
			try {
				closeConnection(ds, conn, error);
			} catch (Exception e) {
				throw new Exception(
						"rollback in getParameterAsParameterDOM failed");
			}
		}

		// If we get here there's been an error
		return (!error) ? returnDoc : null;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#getParameterSetDescription(int)
	 */
	public String getParameterSetDescription(int psId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(PARAMETERSET_SELECT_DESCRIPTION, psId);
		return executeSqlReturnString(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#getParameterSetParameterIds(int)
	 */
	public ArrayList<Integer> getParameterSetParameterIds(int psId)
			throws Exception {
		String sqlStmt;
		sqlStmt = String.format(PARAMETERSET_SELECT_PARAMETERIDS, psId);
		return executeSqlReturnIntegerArrayList(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#setParameterSetDescription(int,
	 * java.lang.String)
	 */
	public void setParameterSetDescription(int psId, String description)
			throws Exception {
		String sqlStmt;
		String descEnc = myBCodec.encode(description);
		sqlStmt = String.format(PARAMETERSET_SET_DESCRIPTION, descEnc, psId);
		boolean success;
		success = executeSqlReturnBoolean(ds, sqlStmt);
		if (!success)
			throw new Exception("Unable to set ParameterSet " + psId
					+ " description to " + description);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#deleteParameterSet(int)
	 */
	public void deleteParameterSet(int psId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(PARAMETERSET_DELETE, psId);
		boolean success;
		success = executeSqlReturnBoolean(ds, sqlStmt);
		if (!success)
			throw new Exception("Unable to delete ParameterSet " + psId);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#getScenarioParameterValue(int,
	 * int)
	 */
	public String getScenarioParameterValue(int psId, int pId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(PARAMETERSET_SELECT_VALUE, pId, psId);
		return executeSqlReturnString(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#getScenarioParameterComment(int,
	 * int)
	 */
	public String getScenarioParameterComment(int psId, int pId)
			throws Exception {
		String sqlStmt;
		sqlStmt = String.format(PARAMETERSET_SELECT_COMMENT, pId, psId);
		return executeSqlReturnString(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#setScenarioParameterComment(int,
	 * int, java.lang.String)
	 */
	public void setScenarioParameterComment(int psId, int pId, String comment)
			throws Exception {
		String sqlStmt;
		String commentEnc = myBCodec.encode(comment);
		sqlStmt = String
				.format(PARAMETERSET_SET_COMMENT, commentEnc, pId, psId);
		boolean success;
		success = executeSqlReturnBoolean(ds, sqlStmt);
		if (!success)
			throw new Exception("Unable to set ParameterSet " + psId
					+ " parameter " + pId + " comment to " + comment);
	}

	/* ----- Parameter Methods ---- */
	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#addParameter(java.lang.String,
	 * java.lang.String, java.lang.String, java.lang.String, java.lang.String,
	 * int, java.lang.String, java.lang.String)
	 */
	public int addParameter(String pName, String pUnits, String pType,
			String pClass, String pDescr, int psId, String pValue,
			String pValComment) throws Exception {
		String sqlStmt;
		String pNameEnc = myBCodec.encode(pName);
		String pUnitsEnc = myBCodec.encode(pUnits);
		String pTypeEnc = myBCodec.encode(pType);
		String pClassEnc = myBCodec.encode(pClass);
		String pDescrEnc = myBCodec.encode(pDescr);
		String pValueEnc = myBCodec.encode(pValue);
		String pValCommentEnc = myBCodec.encode(pValComment);
		int pId;
		sqlStmt = String.format(PARAMETER_INSERT, pNameEnc, pTypeEnc,
				pUnitsEnc, pClassEnc, pDescrEnc);
		pId = executeSqlReturnIntKey(ds, sqlStmt);
		sqlStmt = String.format(PARAMETER_ADD_PARAMETERTOPARAMETERSET, pId,
				psId, pValueEnc, pValCommentEnc);
		executeSqlReturnIntKey(ds, sqlStmt);
		return pId;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#getParameterAsXML(int)
	 */
	public String getParameterAsXML(int pId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(PARAMETER_SELECT, pId);
		return executeSqlSelectReturnXMLString(sqlStmt, ds);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#getParameterName(int)
	 */
	public String getParameterName(int pId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(PARAMETER_SELECT_NAME, pId);
		return executeSqlReturnString(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#getParameterType(int)
	 */
	public String getParameterType(int pId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(PARAMETER_SELECT_TYPE, pId);
		return executeSqlReturnString(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#getParameterUnits(int)
	 */
	public String getParameterUnits(int pId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(PARAMETER_SELECT_UNITS, pId);
		return executeSqlReturnString(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#getParameterAssociatedClass(int)
	 */
	public String getParameterAssociatedClass(int pId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(PARAMETER_SELECT_CLASS, pId);
		return executeSqlReturnString(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#getParameterDescription(int)
	 */
	public String getParameterDescription(int pId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(PARAMETER_SELECT_DESCRIPTION, pId);
		return executeSqlReturnString(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.cs.usask.ca.silver.api.persistence.StorageI#
	 * getParameterParentParameterSetId(int)
	 */
	public int getParameterParentParameterSetId(int pId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(PARAMETER_SELECT_PARAMSETID, pId);
		return executeSqlReturnInt(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#setParameterUnits(int,
	 * java.lang.String)
	 */
	public void setParameterUnits(int pId, String units) throws Exception {
		String sqlStmt;
		String unitsEnc = myBCodec.encode(units);
		sqlStmt = String.format(PARAMETER_SET_UNITS, unitsEnc, pId);
		boolean success;
		success = executeSqlReturnBoolean(ds, sqlStmt);
		if (!success)
			throw new Exception();

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#setParameterAssociatedClass(int,
	 * java.lang.String)
	 */
	public void setParameterAssociatedClass(int pId, String pClass)
			throws Exception {
		String sqlStmt;
		String pClassEnc = myBCodec.encode(pClass);
		sqlStmt = String.format(PARAMETER_SET_CLASS, pClassEnc, pId);
		boolean success;
		success = executeSqlReturnBoolean(ds, sqlStmt);
		if (!success)
			throw new Exception();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#setParameterDescription(int,
	 * java.lang.String)
	 */
	public boolean setParameterDescription(int pId, String description)
			throws Exception {
		String sqlStmt;
		String descEnc = myBCodec.encode(description);
		sqlStmt = String.format(PARAMETER_SET_DESCRIPTION, descEnc, pId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#addParameterToParameterSet(int,
	 * int, java.lang.String, java.lang.String)
	 */
	public void addParameterToParameterSet(int pId, int psId, String value,
			String comment) throws Exception {
		String sqlStmt;
		String valueEnc = myBCodec.encode(value);
		String commentEnc = myBCodec.encode(comment);
		sqlStmt = String.format(PARAMETER_ADD_PARAMETERTOPARAMETERSET, pId,
				psId, valueEnc, commentEnc);
		boolean success;
		success = executeSqlReturnBoolean(ds, sqlStmt);
		if (!success)
			throw new Exception();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#deleteParameter(int)
	 */
	public boolean deleteParameter(int pId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(PARAMETER_DELETE, pId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/* ----- File Methods ---- */
	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#addFile(java.lang.String,
	 * java.lang.String, java.lang.String)
	 */
	public UUID addFile(String fName, String fDescr, String fileSourceLocation)
			throws Exception {
		String sqlStmt = FILE_INSERT;
		UUID fUUID = UUID.randomUUID();
		String fDescEnc = myBCodec.encode(fDescr);
		String fNameEnc = myBCodec.encode(fName);
		boolean error = false;

		File f = new File(fileSourceLocation);
		if (!f.exists()) {
			error = true;
			throw new IOException(
					"Attempting to save file to DB - File not found: "
							+ fileSourceLocation);
		}

		Connection conn = null;
		PreparedStatement insStmt = null;
		FileInputStream in = null;
		// Create Connection and execute SQL
		try {
			in = new FileInputStream(f);
			byte[] file = new byte[(int) f.length()];
			in.read(file);
			conn = getConnection(ds);
			insStmt = conn.prepareStatement(sqlStmt,
					Statement.NO_GENERATED_KEYS);
			insStmt.setString(1, fUUID.toString());
			insStmt.setString(2, fNameEnc);
			insStmt.setString(3, fDescEnc);
			insStmt.setBytes(4, file);
			insStmt.executeUpdate();
			insStmt.close();
		} catch (SQLException sqlExcept) {
			error = true;

			throw sqlExcept;
		} catch (Exception except) {
			error = true;
			//
			throw except;
		} finally {
			try {
				in.close();
				closeConnection(ds, conn, error);
			} catch (Exception e) {
				error = true;
				throw new Exception("rollback failed in addFile");
			}
		}

		return (!error) ? fUUID : null;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#getFName(int)
	 */
	public String getFName(UUID fId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(FILE_SELECT_NAME, fId);
		return executeSqlReturnString(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#getFDescription(int)
	 */
	public String getFDescription(UUID fId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(FILE_SELECT_DESCRIPTION, fId);
		return executeSqlReturnString(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#getFFileToLocalStorage(int,
	 * java.lang.String)
	 * 
	 * TODO: use File instead of destination
	 */
	public void getFFileToLocalStorage(UUID fId, File destination)
			throws Exception {
		String sqlStmt;
		// FILE_SELECT_DATA = "SELECT f_data FROM File WHERE f_id = %d";
		sqlStmt = String.format(FILE_SELECT_DATA, fId);
		boolean error = false;
		Connection conn = null;
		Statement stmt = null;
		// Create Connection and execute SQL
		try {
			conn = getConnection(ds);

			ResultSet rset;
			Blob outFile;
			stmt = conn.createStatement();
			rset = stmt.executeQuery(sqlStmt);
			rset.next();
			outFile = rset.getBlob("f_data");

			rset.close();
			stmt.close();

			FileOutputStream output = new FileOutputStream(destination);
			output.write(outFile.getBytes(1, (int) outFile.length()));
			output.flush();
			output.close();

			logger.info("SAVED: " + destination.getAbsolutePath());

		} catch (Exception except) {

			error = true;
			throw except;
		} finally {
			try {
				closeConnection(ds, conn, error);
			} catch (Exception e) {
				throw new Exception("roll back failed in getFileToLocalStorage");
			}
		}
	}

	public boolean setFName(UUID fId, String name) throws Exception {
		String sqlStmt;
		String nameEnc = myBCodec.encode(name);
		sqlStmt = String.format(FILE_SET_NAME, nameEnc, fId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#setFDescription(int,
	 * java.lang.String)
	 */
	public boolean setFDescription(UUID fId, String description)
			throws Exception {
		String sqlStmt;
		String descEnc = myBCodec.encode(description);
		sqlStmt = String.format(FILE_SET_DESCRIPTION, descEnc, fId);
		return executeSqlReturnBoolean(ds, sqlStmt);

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#addFFileToScenarioAsInputFile
	 * (int, int)
	 */
	public boolean addFFileToScenarioAsInputFile(UUID fId, UUID sId)
			throws Exception {
		String sqlStmt;
		sqlStmt = String.format(INPUTFILE_INSERT, fId, sId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#addFFileToScenarioAsTrajectoryFile
	 * (int, int)
	 */
	public boolean addFFileToScenarioAsTrajectoryFile(UUID fId, UUID sId)
			throws Exception {
		String sqlStmt;
		sqlStmt = String.format(TRAJECTORYFILE_INSERT, fId, sId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#addFFileToScenarioAsAnalysisFile
	 * (int, int)
	 */
	public boolean addFFileToScenarioAsAnalysisFile(UUID fId, UUID sId)
			throws Exception {
		String sqlStmt;
		sqlStmt = String.format(ANALYSISFILE_INSERT, fId, sId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#addFileToScenarioCollection(int,
	 * int)
	 */
	public boolean addFileToScenarioCollection(UUID fId, UUID scId)
			throws Exception {
		String sqlStmt;
		sqlStmt = String.format(FILE_ADDTO_SCENARIOCOLLECTION, fId, scId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#addFileToScenarioCollection(int,
	 * int)
	 */
	public boolean addFileToScenario(UUID fId, UUID sId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(FILE_ADDTO_SCENARIO, fId, sId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#addFileToScenarioCollection(int,
	 * int)
	 */
	public boolean addFileToModelProject(UUID fId, UUID mpId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(FILE_ADDTO_MODELPROJECT, fId, mpId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#addFileToScenarioCollection(int,
	 * int)
	 */
	public boolean addFileToModelVersion(UUID fId, UUID mvId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(FILE_ADDTO_MODELVERSION, fId, mvId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#deleteFile(java.lang.String)
	 */
	public boolean deleteFile(UUID fId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(FILE_DELETE, fId);
		return executeSqlReturnBoolean(ds, sqlStmt);
	}

	@Override
	public String getScenarioUndecodedParameterSetInfoAsXML(UUID sId)
			throws Exception {
		return executeStoredProcedureReturnString(ds,
				CALL_PROCEDURE_PRINT_SCENARIO_PS_TO_XML, sId);
	}

	@Override
	public String getScenarioCollectionUndecodedInfoAsXML(UUID scId)
			throws Exception {
		return executeStoredProcedureReturnString(ds,
				CALL_PROCEDURE_PRINT_SCENARIO_COLLECTION_TO_XML, scId);
	}

	@Override
	public String getModelVersionUndecodedInfoAsXML(UUID mvId) throws Exception {
		return executeStoredProcedureReturnString(ds,
				CALL_PROCEDURE_PRINT_MODEL_VERSION_TO_XML, mvId);
	}

	@Override
	public String getModelProjectUndecodedInfoAsXML(UUID mpId) throws Exception {
		return executeStoredProcedureReturnString(ds,
				CALL_PROCEDURE_PRINT_MODEL_PROJECT_TO_XML, mpId);
	}

	@Override
	public String getGroupUndecodedInfoAsXML(UUID gId) throws Exception {
		return executeStoredProcedureReturnString(ds,
				CALL_PROCEDURE_PRINT_GROUP_TO_XML, gId);
	}

	@Override
	public String getUserUndecodedInfoAsXML(UUID uId) throws Exception {
		return executeStoredProcedureReturnString(ds,
				CALL_PROCEDURE_PRINT_USER_TO_XML, uId);
	}
	
	
	/**
	 * Remote procedure implementation
	 */
	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.cs.usask.ca.silver.api.persistence.StorageI#duplicateFile(java.util
	 * .UUID)
	 */
	public UUID duplicateFile(UUID fId) throws Exception {
		String sqlStmt;
		UUID fUUID = UUID.randomUUID();
		sqlStmt = String.format(FILE_DUPLICATE, fUUID, fId, fId, fId);
		if (executeSqlReturnBoolean(ds, sqlStmt))
			return fUUID;

		return null;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * This is not transactional, but could be transactional
	 * 
	 * @see
	 * edu.cs.usask.ca.silver.api.persistence.StorageI#duplicateScenario(java
	 * .util.UUID, java.util.UUID)
	 */
	public UUID duplicateScenario(UUID sId, UUID scId) throws Exception {
		UUID newSId = UUID.randomUUID();
		String sqlStmt = String.format(CALL_PROCEDURE_DUPLICATE_SCENARIO, sId,
				newSId, scId);
		executeSql(ds, sqlStmt);

		return newSId;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.usask.cs.silver.persistence.Storage#duplicateScenarioCollection(int,
	 * int)
	 */
	public UUID duplicateScenarioCollection(UUID scId, UUID mvId)
			throws Exception {
		String sqlStmt;
		UUID newscId = UUID.randomUUID();

		// Copy the source Scenario Collection to the new Scenario Collection
		sqlStmt = String.format(CALL_PROCEDURE_DUPLICATE_SCENARIO_COLLECTION,
				scId, newscId, mvId);
		executeSql(ds, sqlStmt);
		return newscId;
	}

	/*
	 * PostgreSQL (non-Javadoc)
	 * 
	 * @see edu.usask.cs.silver.persistence.Storage#duplicateScenarioWithSP(int,
	 * int)
	 */
	public int duplicateScenarioWithSP(int sId, int scId) throws Exception {
		String spStmt;
		spStmt = String.format(SCENARIO_COPY_SP, sId, scId);
		return executeStoredProcedureReturnInt(ds, spStmt, 3);
	}

	// get all the model project, model version, scenario collection, and
	// scenario ID and names
	public String getWholeTreeAsXML() throws Exception {
		return null;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.cs.usask.ca.silver.api.persistence.StorageI#
	 * duplicateScenarioTrajectoryFile(java.util.UUID, java.util.UUID)
	 */
	public void duplicateScenarioTrajectoryFile(UUID fromSId, UUID toSId)
			throws Exception {
		String sqlStmt = String.format(
				CALL_PROCEDURE_DUPLICATE_TRAJECTORY_FILES_BETWEEN_SCENARIO,
				fromSId, toSId);
		executeSql(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see edu.cs.usask.ca.silver.api.persistence.StorageI#
	 * duplicateScenarioTrajectoryFile(java.util.UUID, java.util.UUID)
	 */
	@Override
	public void duplicateScenarioAssociatedFile(UUID fromSId, UUID toSId)
			throws Exception {
		String sqlStmt = String.format(
				CALL_PROCEDURE_DUPLICATE_ASSOCIATE_FILES_BETWEEN_SCENARIO,
				fromSId, toSId);
		executeSql(ds, sqlStmt);
	}

	public void duplicateScenarioInputFile(UUID fromSId, UUID toSId)
			throws Exception {
		String sqlStmt = String.format(
				CALL_PROCEDURE_DUPLICATE_INPUT_FILES_BETWEEN_SCENARIO, fromSId,
				toSId);
		executeSql(ds, sqlStmt);
	}

	public void duplicateScenarioAnalysisFile(UUID fromSId, UUID toSId)
			throws Exception {
		String sqlStmt = String.format(
				CALL_PROCEDURE_DUPLICATE_ANALYSIS_FILES_BETWEEN_SCENARIO,
				fromSId, toSId);
		executeSql(ds, sqlStmt);
	}

	// //// Data duplicating within database

	public void duplicateParametersBetweenParameterSets(int psIdFrom, int psIdTo)
			throws Exception {
		String sqlStmt = String.format(
				CALL_PROCEDURE_DUPLICATE_PARAMETERS_BETWEEN_PS, psIdFrom,
				psIdTo);
		executeSql(ds, sqlStmt);
	}

	/**
	 * Remote procedure of select user (not to be used in other means).
	 * 
	 * @param data1
	 *            - the array to be returned
	 * @throws SQLException
	 */
	public static void selectUserProcedure(ResultSet[] data1)
			throws SQLException {
		Connection conn = getDerbyProcedureConnection();
		PreparedStatement ps1 = conn.prepareStatement(USER_SELECT_ALLIDS);
		data1[0] = ps1.executeQuery();
		conn.close();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.cs.usask.ca.silver.api.persistence.StorageI#duplicateParameterSetRow
	 * (int)
	 */
	public int duplicateParameterSetRow(int psId) throws Exception {
		String sqlStmt;
		sqlStmt = String.format(PARAMETERSET_COPY, psId);

		return executeSqlReturnIntKey(ds, sqlStmt);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.cs.usask.ca.silver.api.persistence.StorageI#duplicateParameterRow
	 * (int)
	 */
	protected int duplicateParameterRow(int pId) throws Exception {
		String sqlStmt = String.format(CALL_PROCEDURE_DUPLICATE_PARAMETER, pId);
		return executeSqlReturnInt(ds, sqlStmt);
	}

	// ///////////////////////////////////////////////////////////////////////////////
	// Derby Procedures
	//
	// No explicit transaction control in derby proceture call
	// @see http://db.apache.org/derby/manuals/develop/develop63.html
	// ///////////////////////////////////////////////////////////////////////////////

	/*
	 * Helper function to create a default connection (to be called by Derby
	 * within Derby) for procedures
	 */
	private static Connection getDerbyProcedureConnection() throws SQLException {
		Connection conn = DriverManager
				.getConnection("jdbc:default:connection");
		conn.setAutoCommit(false); // all procedures will be transactional
		return conn;
	}

	/**
	 * Stored procedure to be registered in Derby database.
	 * 
	 * CREATE PROCEDURE duplicate_Scenario( IN sId CHAR(36), IN scId CHAR(36),
	 * OUT newSId(36) )
	 * 
	 * PARAMETERR STYLE JAVA LANGUAGE JAVA EXTERNAL NAME
	 * 'edu.cs.usask.ca.silver.persistence.datasource.DerbyStorage.duplicateScen
	 * a r i o ' ;
	 */
	public static void duplicateScenarioProcedure(String fromSIdString,
			String toSIdString, String toScIdString) throws SQLException {
		Connection conn = getDerbyProcedureConnection();

		UUID fromScenarioId = UUID.fromString(fromSIdString);
		UUID toSId = UUID.fromString(toSIdString);
		UUID toScId = UUID.fromString(toScIdString);
		UUID newVersion = UUID.randomUUID();

		// duplicate Scenario Parameter Set
		String sqlStmt = String.format(SCENARIO_SELECT_PSID, fromScenarioId);
		PreparedStatement preparedStmt = conn.prepareStatement(sqlStmt);
		ResultSet fromPSSet = preparedStmt.executeQuery();
		int fromPSSetId = -1;
		while (fromPSSet.next()) {
			fromPSSetId = Integer.valueOf(fromPSSet.getInt(1));
		}

		// duplicate Parameter set
		sqlStmt = String.format(PARAMETERSET_COPY, fromPSSetId);
		preparedStmt = conn.prepareStatement(sqlStmt,
				Statement.RETURN_GENERATED_KEYS);
		preparedStmt.execute();
		ResultSet toPSSet = preparedStmt.getGeneratedKeys();
		int toPSSetId = -1;

		while (toPSSet.next()) {
			toPSSetId = Integer.valueOf(toPSSet.getInt(1));
		}

		// duplicate Parameter Set Scenarios
		sqlStmt = String.format(PARAMETERSET_SELECT_PARAMETERIDS, fromPSSetId);
		preparedStmt = conn.prepareStatement(sqlStmt);
		ResultSet fromParameters = preparedStmt.executeQuery();
		ResultSet temp;
		// Extract the ArrayList from the ResultSet
		while (fromParameters.next()) {
			// iterate through all parameters
			int fromParameterId = Integer.valueOf(fromParameters.getInt(1));
			sqlStmt = String.format(PARAMETER_COPY, fromParameterId,
					fromParameterId, fromParameterId, fromParameterId,
					fromParameterId);
			preparedStmt = conn.prepareStatement(sqlStmt,
					Statement.RETURN_GENERATED_KEYS);
			preparedStmt.execute();

			temp = preparedStmt.getGeneratedKeys();
			if (temp.next()) {
				sqlStmt = String.format(PARAMETER_ADD_PARAMETERTOPARAMETERSET,
						temp.getInt(1), toPSSetId, "", "");
				preparedStmt = conn.prepareStatement(sqlStmt);
				preparedStmt.execute();
			}
		}

		// duplicate scenario
		sqlStmt = String.format(SCENARIO_COPY, toSId, newVersion,
				fromScenarioId, fromScenarioId, fromScenarioId, fromScenarioId,
				fromScenarioId, fromScenarioId, fromScenarioId, fromScenarioId,
				toScId, toPSSetId);
		preparedStmt = conn.prepareStatement(sqlStmt);
		preparedStmt.execute();

		// duplicateScenarioTrajectoryFile
		sqlStmt = String.format(SCENARIO_SELECT_TRAJECTORYFILEIDS,
				fromScenarioId);
		preparedStmt = conn.prepareStatement(sqlStmt);
		temp = preparedStmt.executeQuery();
		while (temp.next()) {
			// iterate through all parameters
			UUID result = UUID.fromString(temp.getString(1)); // trajectory
																// file
																// that is to be
																// copied
			UUID fileId = UUID.randomUUID();
			sqlStmt = String.format(FILE_DUPLICATE, fileId, result, result,
					result);
			preparedStmt = conn.prepareStatement(sqlStmt,
					Statement.NO_GENERATED_KEYS);
			preparedStmt.execute();

			sqlStmt = String.format(TRAJECTORYFILE_INSERT, fileId, toSId);
			preparedStmt = conn.prepareStatement(sqlStmt);
			preparedStmt.execute();
		}
		// duplicateScenarioAnalysisFile

		sqlStmt = String
				.format(SCENARIO_SELECT_ANALYSISFILEIDS, fromScenarioId);
		preparedStmt = conn.prepareStatement(sqlStmt);
		temp = preparedStmt.executeQuery();
		while (temp.next()) {
			// iterate through all parameters
			UUID result = UUID.fromString(temp.getString(1)); // trajectory
																// file
																// that is to be
																// copied
			UUID fileId = UUID.randomUUID();
			sqlStmt = String.format(FILE_DUPLICATE, fileId, result, result,
					result);
			preparedStmt = conn.prepareStatement(sqlStmt,
					Statement.NO_GENERATED_KEYS);
			preparedStmt.execute();

			sqlStmt = String.format(ANALYSISFILE_INSERT, fileId, toSId);
			preparedStmt = conn.prepareStatement(sqlStmt);
			preparedStmt.execute();
		}

		// duplicateScenarioInputFile
		sqlStmt = String.format(SCENARIO_SELECT_INPUTFILEIDS, fromScenarioId);
		preparedStmt = conn.prepareStatement(sqlStmt);
		temp = preparedStmt.executeQuery();
		while (temp.next()) {
			// iterate through all parameters
			UUID result = UUID.fromString(temp.getString(1)); // trajectory
																// file
																// that is to be
																// copied
			UUID fileId = UUID.randomUUID();
			sqlStmt = String.format(FILE_DUPLICATE, fileId, result, result,
					result);
			preparedStmt = conn.prepareStatement(sqlStmt,
					Statement.NO_GENERATED_KEYS);
			preparedStmt.execute();

			sqlStmt = String.format(INPUTFILE_INSERT, fileId, toSId);
			preparedStmt = conn.prepareStatement(sqlStmt);
			preparedStmt.execute();
		}

		// duplicateScenarioAssociatedFile
		sqlStmt = String
				.format(SCENARIO_SELECT_ANALYSISFILEIDS, fromScenarioId);
		preparedStmt = conn.prepareStatement(sqlStmt);
		temp = preparedStmt.executeQuery();
		while (temp.next()) {
			// iterate through all parameters
			UUID result = UUID.fromString(temp.getString(1)); // trajectory
																// file
																// that is to be
																// copied
			UUID fileId = UUID.randomUUID();
			sqlStmt = String.format(FILE_DUPLICATE, fileId, result, result,
					result);
			preparedStmt = conn.prepareStatement(sqlStmt,
					Statement.NO_GENERATED_KEYS);
			preparedStmt.execute();

			sqlStmt = String.format(ANALYSISFILE_INSERT, fileId, toSId);
			preparedStmt = conn.prepareStatement(sqlStmt);
			preparedStmt.execute();
		}

		conn.close();
	}

	public static void duplicateScenarioCollectionProcedure(String fromScId,
			String toScId, String toMvId) throws SQLException {
		Connection conn = getDerbyProcedureConnection();
		UUID newversion = UUID.randomUUID();
		// duplicate scenario collection
		// new id, new version, old id, old id, old id, mv id
		String sqlStmt = String.format(SCOLLECTION_COPY, toScId, newversion,
				fromScId, fromScId, fromScId, toMvId);
		PreparedStatement ps = conn.prepareStatement(sqlStmt);
		ps.execute();

		// duplicate scenario collection associated files
		sqlStmt = String.format(SCOLLECTION_SELECT_ASSOCIATEDFILEIDS, fromScId);
		PreparedStatement ps1 = conn.prepareStatement(sqlStmt);
		ResultSet rs = ps1.executeQuery();
		while (rs.next()) {
			// iterate through all parameters
			UUID result = UUID.fromString(rs.getString(1)); // trajectory file
															// that is to be
															// copied
			UUID fileId = UUID.randomUUID();
			sqlStmt = String.format(FILE_DUPLICATE, fileId, result, result,
					result);
			ps1 = conn.prepareStatement(sqlStmt, Statement.NO_GENERATED_KEYS);
			ps1.execute();

			sqlStmt = String.format(FILE_ADDTO_SCENARIOCOLLECTION, fileId,
					toScId);
			ps1 = conn.prepareStatement(sqlStmt);
			ps1.execute();
		}

		// duplicate scenario collection scenarios
		sqlStmt = String.format(SCOLLECTION_SELECT_SCENARIOIDS, fromScId);
		PreparedStatement ps2 = conn.prepareStatement(sqlStmt);
		ResultSet rsScenario = ps2.executeQuery();
		PreparedStatement preparedStmt;
		ResultSet temp;
		while (rsScenario.next()) {
			UUID fromScenarioId = UUID.fromString(rsScenario.getString(1));
			UUID toSId = UUID.randomUUID();
			UUID newVersion = UUID.randomUUID();

			// duplicate Scenario Parameter Set
			sqlStmt = String.format(SCENARIO_SELECT_PSID, fromScenarioId);
			preparedStmt = conn.prepareStatement(sqlStmt);
			ResultSet fromPSSet = preparedStmt.executeQuery();
			int fromPSSetId = -1;
			while (fromPSSet.next()) {
				fromPSSetId = Integer.valueOf(fromPSSet.getInt(1));
			}

			// duplicate Parameter set
			sqlStmt = String.format(PARAMETERSET_COPY, fromPSSetId);
			preparedStmt = conn.prepareStatement(sqlStmt,
					Statement.RETURN_GENERATED_KEYS);
			preparedStmt.execute();
			ResultSet toPSSet = preparedStmt.getGeneratedKeys();
			int toPSSetId = -1;

			while (toPSSet.next()) {
				toPSSetId = Integer.valueOf(toPSSet.getInt(1));
			}

			// duplicate Parameter Set Scenarios
			sqlStmt = String.format(PARAMETERSET_SELECT_PARAMETERIDS,
					fromPSSetId);
			preparedStmt = conn.prepareStatement(sqlStmt);
			ResultSet fromParameters = preparedStmt.executeQuery();

			// Extract the ArrayList from the ResultSet
			while (fromParameters.next()) {
				// iterate through all parameters
				int fromParameterId = Integer.valueOf(fromParameters.getInt(1));
				sqlStmt = String.format(PARAMETER_COPY, fromParameterId,
						fromParameterId, fromParameterId, fromParameterId,
						fromParameterId);
				preparedStmt = conn.prepareStatement(sqlStmt,
						Statement.RETURN_GENERATED_KEYS);
				preparedStmt.execute();

				temp = preparedStmt.getGeneratedKeys();
				if (temp.next()) {
					sqlStmt = String.format(
							PARAMETER_ADD_PARAMETERTOPARAMETERSET,
							temp.getInt(1), toPSSetId, "", "");
					preparedStmt = conn.prepareStatement(sqlStmt);
					preparedStmt.execute();
				}
			}

			// duplicate scenario
			sqlStmt = String.format(SCENARIO_COPY, toSId, newVersion,
					fromScenarioId, fromScenarioId, fromScenarioId,
					fromScenarioId, fromScenarioId, fromScenarioId,
					fromScenarioId, fromScenarioId, toScId, toPSSetId);
			preparedStmt = conn.prepareStatement(sqlStmt);
			preparedStmt.execute();

			// duplicateScenarioTrajectoryFile
			sqlStmt = String.format(SCENARIO_SELECT_TRAJECTORYFILEIDS,
					fromScenarioId);
			preparedStmt = conn.prepareStatement(sqlStmt);
			temp = preparedStmt.executeQuery();
			while (temp.next()) {
				// iterate through all parameters
				UUID result = UUID.fromString(temp.getString(1)); // trajectory
																	// file
																	// that is
																	// to be
																	// copied
				UUID fileId = UUID.randomUUID();
				sqlStmt = String.format(FILE_DUPLICATE, fileId, result, result,
						result);
				preparedStmt = conn.prepareStatement(sqlStmt,
						Statement.NO_GENERATED_KEYS);
				preparedStmt.execute();

				sqlStmt = String.format(TRAJECTORYFILE_INSERT, fileId, toSId);
				preparedStmt = conn.prepareStatement(sqlStmt);
				preparedStmt.execute();
			}
			// duplicateScenarioAnalysisFile

			sqlStmt = String.format(SCENARIO_SELECT_ANALYSISFILEIDS,
					fromScenarioId);
			preparedStmt = conn.prepareStatement(sqlStmt);
			temp = preparedStmt.executeQuery();
			while (temp.next()) {
				// iterate through all parameters
				UUID result = UUID.fromString(temp.getString(1)); // trajectory
																	// file
																	// that is
																	// to be
																	// copied
				UUID fileId = UUID.randomUUID();
				sqlStmt = String.format(FILE_DUPLICATE, fileId, result, result,
						result);
				preparedStmt = conn.prepareStatement(sqlStmt,
						Statement.NO_GENERATED_KEYS);
				preparedStmt.execute();

				sqlStmt = String.format(ANALYSISFILE_INSERT, fileId, toSId);
				preparedStmt = conn.prepareStatement(sqlStmt);
				preparedStmt.execute();
			}

			// duplicateScenarioInputFile
			sqlStmt = String.format(SCENARIO_SELECT_INPUTFILEIDS,
					fromScenarioId);
			preparedStmt = conn.prepareStatement(sqlStmt);
			temp = preparedStmt.executeQuery();
			while (temp.next()) {
				// iterate through all parameters
				UUID result = UUID.fromString(temp.getString(1)); // trajectory
																	// file
																	// that is
																	// to be
																	// copied
				UUID fileId = UUID.randomUUID();
				sqlStmt = String.format(FILE_DUPLICATE, fileId, result, result,
						result);
				preparedStmt = conn.prepareStatement(sqlStmt,
						Statement.NO_GENERATED_KEYS);
				preparedStmt.execute();

				sqlStmt = String.format(INPUTFILE_INSERT, fileId, toSId);
				preparedStmt = conn.prepareStatement(sqlStmt);
				preparedStmt.execute();
			}

			// duplicateScenarioAssociatedFile
			sqlStmt = String.format(SCENARIO_SELECT_ANALYSISFILEIDS,
					fromScenarioId);
			preparedStmt = conn.prepareStatement(sqlStmt);
			temp = preparedStmt.executeQuery();
			while (temp.next()) {
				// iterate through all parameters
				UUID result = UUID.fromString(temp.getString(1)); // trajectory
																	// file
																	// that is
																	// to be
																	// copied
				UUID fileId = UUID.randomUUID();
				sqlStmt = String.format(FILE_DUPLICATE, fileId, result, result,
						result);
				preparedStmt = conn.prepareStatement(sqlStmt,
						Statement.NO_GENERATED_KEYS);
				preparedStmt.execute();

				sqlStmt = String.format(ANALYSISFILE_INSERT, fileId, toSId);
				preparedStmt = conn.prepareStatement(sqlStmt);
				preparedStmt.execute();
			}
		}

		conn.close();
	}

	/**
	 * A Derby procedure procedure to duplicate parameter row
	 * 
	 * @param pId
	 * @param data1
	 * @throws SQLException
	 */
	public static void duplicateParameterProcedure(int pId, ResultSet[] data1)
			throws SQLException {
		Connection conn = getDerbyProcedureConnection();
		String sqlStmt = String.format(PARAMETER_COPY, pId, pId, pId, pId, pId);
		PreparedStatement ps1 = conn.prepareStatement(sqlStmt,
				Statement.RETURN_GENERATED_KEYS);
		ps1.execute();
		data1[0] = ps1.getGeneratedKeys();
		conn.close();
	}

	/**
	 * A Derby procedure to duplicate trajectory
	 * 
	 * @param fromSId
	 * @param toSId
	 * @throws SQLException
	 */
	public static void duplicateScenarioTrajectoryFilesProcedure(
			String fromSId, String toSId) throws SQLException {
		Connection conn = getDerbyProcedureConnection();
		String sqlStmt = String.format(SCENARIO_SELECT_TRAJECTORYFILEIDS,
				fromSId);
		PreparedStatement ps1 = conn.prepareStatement(sqlStmt);
		ResultSet rs = ps1.executeQuery();
		while (rs.next()) {
			// iterate through all parameters
			UUID result = UUID.fromString(rs.getString(1)); // trajectory file
															// that is to be
															// copied
			UUID fileId = UUID.randomUUID();
			sqlStmt = String.format(FILE_DUPLICATE, fileId, result, result,
					result);
			ps1 = conn.prepareStatement(sqlStmt, Statement.NO_GENERATED_KEYS);
			ps1.execute();

			sqlStmt = String.format(TRAJECTORYFILE_INSERT, fileId, toSId);
			ps1 = conn.prepareStatement(sqlStmt);
			ps1.execute();
		}

		conn.close();
	}

	/**
	 * A Derby procedure to duplicate trajectory
	 * 
	 * @param fromSId
	 * @param toSId
	 * @throws SQLException
	 */
	public static void duplicateScenarioAssociateFilesProcedure(String fromSId,
			String toSId) throws SQLException {
		Connection conn = getDerbyProcedureConnection();
		String sqlStmt = String.format(SCENARIO_SELECT_ASSOCIATEDFILEIDS,
				fromSId);
		PreparedStatement ps1 = conn.prepareStatement(sqlStmt);
		ResultSet rs = ps1.executeQuery();
		while (rs.next()) {
			// iterate through all parameters
			UUID result = UUID.fromString(rs.getString(1)); // trajectory file
															// that is to be
															// copied
			UUID fileId = UUID.randomUUID();
			sqlStmt = String.format(FILE_DUPLICATE, fileId, result, result,
					result);
			ps1 = conn.prepareStatement(sqlStmt, Statement.NO_GENERATED_KEYS);
			ps1.execute();

			sqlStmt = String.format(FILE_ADDTO_SCENARIO, fileId, toSId);
			ps1 = conn.prepareStatement(sqlStmt);
			ps1.execute();
		}

		conn.close();
	}

	public static void duplicateScenarioCollectionAssociateFilesProcedure(
			String fromScId, String toScId) throws SQLException {
		Connection conn = getDerbyProcedureConnection();
		String sqlStmt = String.format(SCOLLECTION_SELECT_ASSOCIATEDFILEIDS,
				fromScId);
		PreparedStatement ps1 = conn.prepareStatement(sqlStmt);
		ResultSet rs = ps1.executeQuery();
		while (rs.next()) {
			// iterate through all parameters
			UUID result = UUID.fromString(rs.getString(1)); // trajectory file
															// that is to be
															// copied
			UUID fileId = UUID.randomUUID();
			sqlStmt = String.format(FILE_DUPLICATE, fileId, result, result,
					result);
			ps1 = conn.prepareStatement(sqlStmt, Statement.NO_GENERATED_KEYS);
			ps1.execute();

			sqlStmt = String.format(FILE_ADDTO_SCENARIOCOLLECTION, fileId,
					toScId);
			ps1 = conn.prepareStatement(sqlStmt);
			ps1.execute();
		}

		conn.close();
	}

	/**
	 * A Derby procedure to duplicate input file
	 * 
	 * @param fromSId
	 * @param toSId
	 * @throws Exception
	 */
	public static void duplicateScenarioInputFilesProcedure(String fromSId,
			String toSId) throws Exception {
		Connection conn = getDerbyProcedureConnection();
		String sqlStmt = String.format(SCENARIO_SELECT_INPUTFILEIDS, fromSId);
		PreparedStatement ps1 = conn.prepareStatement(sqlStmt);
		ResultSet rs = ps1.executeQuery();
		while (rs.next()) {
			// iterate through all parameters
			UUID result = UUID.fromString(rs.getString(1)); // trajectory file
															// that is to be
															// copied
			UUID fileId = UUID.randomUUID();
			sqlStmt = String.format(FILE_DUPLICATE, fileId, result, result,
					result);
			ps1 = conn.prepareStatement(sqlStmt, Statement.NO_GENERATED_KEYS);
			ps1.execute();

			sqlStmt = String.format(INPUTFILE_INSERT, fileId, toSId);
			ps1 = conn.prepareStatement(sqlStmt);
			ps1.execute();
		}

		conn.close();
	}

	public static void duplicateScenarioAnalysisFilesProcedure(String fromSId,
			String toSId) throws Exception {
		Connection conn = getDerbyProcedureConnection();
		String sqlStmt = String
				.format(SCENARIO_SELECT_ANALYSISFILEIDS, fromSId);
		PreparedStatement ps1 = conn.prepareStatement(sqlStmt);
		ResultSet rs = ps1.executeQuery();
		while (rs.next()) {
			// iterate through all parameters
			UUID result = UUID.fromString(rs.getString(1)); // trajectory file
															// that is to be
															// copied
			UUID fileId = UUID.randomUUID();
			sqlStmt = String.format(FILE_DUPLICATE, fileId, result, result,
					result);
			ps1 = conn.prepareStatement(sqlStmt, Statement.NO_GENERATED_KEYS);
			ps1.execute();

			sqlStmt = String.format(ANALYSISFILE_INSERT, fileId, toSId);
			ps1 = conn.prepareStatement(sqlStmt);
			ps1.execute();
		}

		conn.close();
	}

	/**
	 * A remote procedure to duplicate parameters between two parameter sets
	 * 
	 * @param psIdFrom
	 *            - parameter set id to copy from
	 * @param psIdTo
	 *            - parameter set id to copy to
	 * @throws SQLException
	 */
	public static void duplicateParamameterSetParametersProcedure(int psIdFrom,
			int psIdTo) throws SQLException {
		Connection conn = getDerbyProcedureConnection();
		String sqlStmt = String.format(PARAMETERSET_SELECT_PARAMETERIDS,
				psIdFrom);
		PreparedStatement ps1 = conn.prepareStatement(sqlStmt);
		ResultSet rs = ps1.executeQuery();
		// Extract the ArrayList from the ResultSet
		ResultSet temp;
		while (rs.next()) {
			// iterate through all parameters
			int result = Integer.valueOf(rs.getInt(1));
			sqlStmt = String.format(PARAMETER_COPY, result, result, result,
					result, result);
			ps1 = conn.prepareStatement(sqlStmt,
					Statement.RETURN_GENERATED_KEYS);
			ps1.execute();
			temp = ps1.getGeneratedKeys();
			if (temp.next()) {
				sqlStmt = String.format(PARAMETER_ADD_PARAMETERTOPARAMETERSET,
						temp.getInt(1), psIdTo, "", "");
				ps1 = conn.prepareStatement(sqlStmt);
				ps1.execute();
			}
		}

		conn.close();
	}

	public static void getXMLRepresentationForUserProcedure(String fromUser,
			String[] userXMLRepresentation) throws SQLException {
		Connection conn = getDerbyProcedureConnection();
		try {
			DocumentBuilderFactory factory = DocumentBuilderFactory
					.newInstance();
			DocumentBuilder builder = factory.newDocumentBuilder();
			Document returnDoc = builder.newDocument();

			userXMLRepresentation[0] = buildXMLReportForUserProcedure(null,
					fromUser, conn, builder, returnDoc);
		} catch (Exception except) {
			except.printStackTrace();
		}
		conn.close();
	}

	public static void getXMLRepresentationForGroupProcedure(String fromGroup,
			String[] groupXMLRepresentation) throws SQLException {
		Connection conn = getDerbyProcedureConnection();
		try {
			DocumentBuilderFactory factory = DocumentBuilderFactory
					.newInstance();
			DocumentBuilder builder = factory.newDocumentBuilder();
			Document returnDoc = builder.newDocument();

			groupXMLRepresentation[0] = buildXMLReportForGroupProcedure(null,
					fromGroup, conn, builder, returnDoc);
		} catch (Exception except) {
			except.printStackTrace();
		}
		conn.close();
	}

	public static void getXMLRepresentationForModelProjectProcedure(
			String fromModelProject, String[] modelprojectXMLRepresentation)
			throws SQLException {
		Connection conn = getDerbyProcedureConnection();
		try {
			DocumentBuilderFactory factory = DocumentBuilderFactory
					.newInstance();
			DocumentBuilder builder = factory.newDocumentBuilder();
			Document returnDoc = builder.newDocument();

			// build parent element here

			modelprojectXMLRepresentation[0] = buildXMLReportForModelProjectProcedure(
					null, fromModelProject, conn, builder, returnDoc);
		} catch (Exception except) {
			except.printStackTrace();
		}
		conn.close();
	}

	public static void getXMLRepresentationForModelVersionProcedure(
			String fromModelVersion, String[] modelversionXMLRepresentation)
			throws SQLException {
		Connection conn = getDerbyProcedureConnection();
		try {
			DocumentBuilderFactory factory = DocumentBuilderFactory
					.newInstance();
			DocumentBuilder builder = factory.newDocumentBuilder();
			Document returnDoc = builder.newDocument();
			modelversionXMLRepresentation[0] = buildXMLReportForModelVersionProcedure(
					null, fromModelVersion, conn, builder, returnDoc);
		} catch (Exception except) {
			except.printStackTrace();
		}
		conn.close();
	}

	public static void getXMLRepresentationForScenarioCollectionProcedure(
			String fromScenarioCollection,
			String[] scenarioCollectionXMLRepresentation) throws SQLException {
		Connection conn = getDerbyProcedureConnection();
		try {
			DocumentBuilderFactory factory = DocumentBuilderFactory
					.newInstance();
			DocumentBuilder builder = factory.newDocumentBuilder();
			Document returnDoc = builder.newDocument();

			scenarioCollectionXMLRepresentation[0] = buildXMLReportForScenarioCollectionProcedure(
					null, fromScenarioCollection, conn, builder, returnDoc);
		} catch (Exception except) {
			except.printStackTrace();
		}
		conn.close();
	}

	public static void getXMLRepresentationForScenarioProcedure(
			String fromScenarioId, String[] scenarioXMLRepresentation)
			throws SQLException {
		Connection conn = getDerbyProcedureConnection();
		try {
			DocumentBuilderFactory factory = DocumentBuilderFactory
					.newInstance();
			DocumentBuilder builder = factory.newDocumentBuilder();
			Document returnDoc = builder.newDocument();

			scenarioXMLRepresentation[0] = buildXMLReportForScenarioProcedure(
					null, fromScenarioId, conn, builder, returnDoc);
		} catch (Exception except) {
			except.printStackTrace();
		}
		conn.close();
	}

	private static String buildXMLReportForUserProcedure(Element parent,
			String fromUserId, Connection conn, DocumentBuilder builder,
			Document returnDoc) throws Exception {
		// represent parameter sets
		String sqlStmt = String.format(USER_SELECT_PARTICIPATED_GROUP_IDS,
				fromUserId);
		PreparedStatement preparedStmt = conn.prepareStatement(sqlStmt);
		ResultSet rs = null;
		String result = null;
		rs = preparedStmt.executeQuery();
		try {
			result = generateXMLRepresentationOfResultSet(parent, "USER",
					fromUserId, rs, conn, builder, returnDoc);
		} catch (Exception except) {
			except.printStackTrace();
		}
		return result;
	}

	private static String buildXMLReportForGroupProcedure(Element parent,
			String fromGroupId, Connection conn, DocumentBuilder builder,
			Document returnDoc) throws Exception {
		// represent parameter sets
		String sqlStmt = String.format(GROUP_SELECT_MPROJECT_ID_AND_NAME,
				fromGroupId);
		PreparedStatement preparedStmt = conn.prepareStatement(sqlStmt);
		ResultSet rs = null;
		rs = preparedStmt.executeQuery();
		String result = null;
		try {
			result = generateXMLRepresentationOfResultSet(parent, "GROUP",
					fromGroupId, rs, conn, builder, returnDoc);
		} catch (Exception except) {
			except.printStackTrace();
		}
		return result;
	}

	private static String buildXMLReportForModelProjectProcedure(
			Element parent, String fromModelProjectId, Connection conn,
			DocumentBuilder builder, Document returnDoc) throws Exception {
		// represent parameter sets
		String sqlStmt = String.format(MODEL_PROJECT_SELECT_MVERSIONIDANDNAME,
				fromModelProjectId);
		PreparedStatement preparedStmt = conn.prepareStatement(sqlStmt);
		ResultSet rs = null;
		rs = preparedStmt.executeQuery();
		String result = null;
		try {
			result = generateXMLRepresentationOfResultSet(parent, "MPROJECT",
					fromModelProjectId, rs, conn, builder, returnDoc);
		} catch (Exception except) {
			except.printStackTrace();
		}

		return result;
	}

	private static String buildXMLReportForModelVersionProcedure(
			Element parent, String fromModelVersionId, Connection conn,
			DocumentBuilder builder, Document returnDoc) throws Exception {
		// represent parameter sets
		String sqlStmt = String.format(MODEL_VERSION_SELECT_SCIDANDNAME,
				fromModelVersionId);
		PreparedStatement preparedStmt = conn.prepareStatement(sqlStmt);
		ResultSet rs = null;
		rs = preparedStmt.executeQuery();
		String result = null;
		try {
			result = generateXMLRepresentationOfResultSet(parent, "MVERSION",
					fromModelVersionId, rs, conn, builder, returnDoc);
		} catch (Exception except) {
			except.printStackTrace();
		}

		return result;

	}

	private static String buildXMLReportForScenarioCollectionProcedure(
			Element parent, String fromScenarioCollectionId, Connection conn,
			DocumentBuilder builder, Document returnDoc) throws Exception {

		// represent parameter sets
		String sqlStmt = String.format(SCOLLECTION_SELECT_SCENARIOIDSANDNAMES,
				fromScenarioCollectionId);
		PreparedStatement preparedStmt = conn.prepareStatement(sqlStmt);
		ResultSet rs = null;
		rs = preparedStmt.executeQuery();
		String result = null;
		try {
			result = generateXMLRepresentationOfResultSet(parent,
					"SCENARIOCOLLECTION", fromScenarioCollectionId, rs, conn,
					builder, returnDoc);
		} catch (Exception except) {
			except.printStackTrace();
		}

		return result;
	}

	private static String buildXMLReportForScenarioProcedure(Element parent,
			String fromScenarioId, Connection conn, DocumentBuilder builder,
			Document returnDoc) throws Exception {
		// represent parameter sets
		String sqlStmt = String.format(SCENARIO_SELECT_PSID, fromScenarioId);
		PreparedStatement preparedStmt = conn.prepareStatement(sqlStmt);
		ResultSet fromPSSet = preparedStmt.executeQuery();
		ResultSet rs = null;
		int fromPSSetId = -1;
		while (fromPSSet.next()) {
			fromPSSetId = Integer.valueOf(fromPSSet.getInt(1));
		}
		sqlStmt = String.format(PARAMETERSET_SELECT_PARAMETERIDS, fromPSSetId);
		preparedStmt = conn.prepareStatement(sqlStmt);

		rs = preparedStmt.executeQuery();
		String result = null;
		try {
			result = generateXMLRepresentationOfResultSet(parent, "SCENARIO",
					fromScenarioId, rs, conn, builder, returnDoc);
		} catch (Exception except) {
			except.printStackTrace();
		}
		return result;
	}

	private static String generateXMLRepresentationOfResultSet(Element parent,
			String fromTable, String fromId, ResultSet rs, Connection conn,
			DocumentBuilder builder, Document returnDoc) throws Exception {

		// we need to supply a name space to apply with xpath
		// Element results = returnDoc.createElement("SILVERCOMPONENT");
		if (parent == null) {
			// ////// turn results set into DOM
			parent = returnDoc.createElement(fromTable);
			Element idElement = returnDoc.createElement("ROOT_IDENTITY");
			idElement.appendChild(returnDoc.createTextNode(fromId));
			parent.appendChild(idElement);

			returnDoc.appendChild(parent);
		}

		ResultSetMetaData rsmd = rs.getMetaData();
		int colCount = rsmd.getColumnCount();
		Element row;
		String tablename;

		String columnName;
		UUID id = null;
		Object value;
		Element node = null;
		while (rs.next()) {
			tablename = rsmd.getTableName(1);
			row = returnDoc.createElement(tablename);

			parent.appendChild(row);

			for (int i = 1; i <= colCount; i++) {
				if (i == 1 && tablename.compareToIgnoreCase("P2PS") != 0)
					id = UUID.fromString(rs.getObject(i).toString());

				columnName = rsmd.getColumnName(i);
				value = rs.getObject(i);
				node = returnDoc.createElement(columnName);
				node.appendChild(returnDoc.createTextNode(value.toString()));
				row.appendChild(node);
			}

			// node = createElement(child node)
			if (tablename.compareToIgnoreCase("P2PS") != 0)
				evaluateNodeAndPrintChildren(tablename, row, id, conn, builder,
						returnDoc);

		}

		// ///////////////
		// Generate the XML

		// set up a transformer
		TransformerFactory transfac = TransformerFactory.newInstance();
		Transformer trans = transfac.newTransformer();
		trans.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "yes");
		trans.setOutputProperty(OutputKeys.INDENT, "yes");

		// create string from xml tree
		StringWriter sw = new StringWriter();
		StreamResult result = new StreamResult(sw);
		DOMSource source = new DOMSource(returnDoc);
		trans.transform(source, result);
		return sw.toString();
	}

	private static void evaluateNodeAndPrintChildren(String tablename,
			Element parent, UUID id, Connection conn, DocumentBuilder builder,
			Document returnDoc) throws Exception {
		if (tablename.equalsIgnoreCase("U2GROUP")) {
			buildXMLReportForGroupProcedure(parent, id.toString(), conn,
					builder, returnDoc);
		} else if (tablename.equalsIgnoreCase("MPROJECT")) {
			buildXMLReportForModelProjectProcedure(parent, id.toString(), conn,
					builder, returnDoc);
		} else if (tablename.equalsIgnoreCase("MVERSION")) {
			buildXMLReportForModelVersionProcedure(parent, id.toString(), conn,
					builder, returnDoc);
		} else if (tablename.equalsIgnoreCase("SCENARIOCOLLECTION")) {
			buildXMLReportForScenarioCollectionProcedure(parent, id.toString(),
					conn, builder, returnDoc);
		} else if (tablename.equalsIgnoreCase("SCENARIO")) {
			buildXMLReportForScenarioProcedure(parent, id.toString(), conn,
					builder, returnDoc);
		} else {
			return;
		}

	}

}
